Count If help

[cmb80]

Hello All, I kind of know how the countif function works.

I need to know how many different names appear in Column A, based on the value of Column B.

So in Column A:B I might have

Adam Apple
Gary Orange
Pete Orange
Adam Orange
Gary Apple
Phil Apple
Craig Apple
Adam Apple

I want to count the unique names in column, that seelct apple - so in this case 4 (as adam apepars twice)

I know how to do =countif(B:B,"apple") but what do i need to add to calculate unique occurances in Col A.

Thanks

[Colin Legg]

Hi,

Assuming the data is in A2:B9, here's one way to do it in a single formula.

=SUM(IF(FREQUENCY(IF(B2:B9="Apple",IF(A2:A9<>"",
    MATCH("~"&A2:A9,A2:A9&"",0))),ROW(A2:A9)-ROW(A2)+1),1))

This is an array formula, so CTRL+SHIFT+ENTER, not just ENTER.

[cmb80]

Hi Colin, thanks for the code - the data is actually a lot larger than the example given - and I already have an array forumla within the sheet so using more (especially for the voulme I require) array formula's will drastically reduce the performance.

Is there anyway without using an array?

[tigertiger]

Is there anyway without using an array?

Try the formular (assuming the data is in A2:B9)

=SUMPRODUCT((MATCH(A2:A9&B2:B9,A2:A9&B2:B9,0)=ROW(INDIRECT("1:"&ROWS(A2:A9))))*(B2:B9="Apple"))

Or (the number of rows of the data is show by a certain number)

=SUMPRODUCT((MATCH(A2:A9&B2:B9,A2:A9&B2:B9,0)=ROW(1:8))*(B2:B9="Apple"))

Where 8 is the number of rows of the data

[Colin Legg]

Sure, you can use a helper column. Before I give an example, are you allowed to sort the data?

[cmb80]

Hi Colin, data can not be sorted, I will try that out tigertiger thank you

[Colin Legg]

Hi,

SUMPRODUCT computes arrays in a similar way to array formulas; it's still going to be slow. Based on the data being in A2:B9 (same as before), put this formula in C2 and copy down to C9:

=COUNTIFS(A2:$A$2,A2,B2:$B$2,B2)

Then this formula will give you the count of distinct names corresponding to Apple:

=COUNTIFS(B2:B9,"Apple",C2:C9,1)

This could be greatly optimised if sorting was allowed.

[cmb80]

Hi there, i've tried it in my sheet (too big to upload on here) and I just get N/A in the cell. However when I look at the function in the function arguments screen it shows 15 (the correct answer)

[Colin Legg]

Hi,

We posted at the same time, so I assume that when you say that you "tried it" you mean that you tried tiger's SUMPRODUCT solution. Let me know how you get on with the COUNTIFS helper column.