Formula for unique records that satisfy a row condition

Question

I want to be able to provide a count of distinct values of one column ("ID", in column B below) where a value in the respective row (eg "Value" in Column C below) meets a criteria (eg <>0) below. As you can see, I can easily compute the list of unique ID's (cell B8, with formula in C8) but can't fathom how to pair this with the discovery of non-zero values.

JvdV · Answer 1 · 2019-05-21T08:48:33.853

1

I've formulated an answer to sort of a similar question here. Particulary handy if you want to extend with multiple criteria.

In your case it would look a bit more symplistic cause you have numeric values (ID's)

{=SUM(--(FREQUENCY(IF(Table1[Value]>0,Table1[ID])Table1[ID])>0))}

Entered through CtrlShiftEnter

edited May 21 '19 at 08:48

answered May 21 '19 at 07:43

JvdV

70,606
8
39
70

2

Would that be Frequency for Interval? FWIW I much prefer your frequency method to the countif method. – Tom Sharpe May 21 '19 at 08:42
1

@TomSharpe, i'm sorry, it should be frequency. Interval is the dutch equivalent. – JvdV May 21 '19 at 08:49

score 0 · Answer 2 · answered May 21 '19 at 07:43

0

You can use the double unary operator (--) to revert a true/false to 0 or 1 then use that in the sumproduct like so:

=SUMPRODUCT((1/COUNTIF($B$3:$B$6,$B$3:$B$6))*(--($C$3:$C$6<>0)))

answered May 21 '19 at 07:43

Glitch_Doctor

2,994
3
16
30

score 0 · Answer 3 · answered May 21 '19 at 10:29

0

Here's another (non array) option

=SUM(($C$3:$C$6<>0)/COUNTIFS($B$3:$B$6,$B$3:$B$6))

Or, if the data is a Table as your image suggests

=SUM((YourTableName[Value]<>0)/COUNTIFS(YourTableName[ID],YourTableName[ID]))

answered May 21 '19 at 10:29

chris neilsen

52,446
10
84
123

Nod to @Glitch 's answer, only added it as some might consider it "simpler" – chris neilsen May 21 '19 at 10:30

Formula for unique records that satisfy a row condition

3 Answers3