count if formula

[Fiona Yorke-Saville]

Hi
I wondered if anyone knew the formula for the following:-

If A5:A10 = A1, then count C5:C10, but do not count duplicate information
(such as there being 1234 in both call C5 and C6, only count this as 1)

Thank you

 

[T. Valko]

In other words, count the unique *numbers* in C5:C10 where A5:A10 = A1?

If that's what you want try this array formula** :

=COUNT(1/FREQUENCY(IF(A5:A10=A1,B5:B10),B5:B10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

Ooops!

=COUNT(1/FREQUENCY(IF(A5:A10=A1,B5:B10),B5:B10))

I used the wrong column references!

Should be:

=COUNT(1/FREQUENCY(IF(A5:A10=A1,C5:C10),C5:C10))

 

[Fiona Yorke-Saville]

Thanks Biff, thats definately what I want to do, but when I enter it on to my
spreadsheet it returns a zeor when there should be 3....any ideas??

The formula makes sense for what I want to do

Fiona

 

[T. Valko]

Did you enter the formula as an array?

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.

Also, any time you edit or change an array formula it must be re-entered as
an array using the key combination.

Another possibility: your numbers really aren't numbers. They may look like
numbers but are actually TEXT. Numeric numbers and TEXT numbers look the
same to you and me but Excel sees them differently. You can test by trying
this formula:

=COUNT(C5:C10)

That will return the count of real numeric numbers in the range.