FREQUENCY with multiple criteria

[PCLIVE]

I'd like to apply some additional criteria to this unique value count.

=SUM(IF(FREQUENCY(IF(C:C=A5,D),D),1))

The array formula works ok without additional criteria. However, I'd also
like to include the rows where cells in E:E equal 5 and F:F equal 4. I've
done a SUMPRODUCT that would determine that without determining the unique
values, but I want to somehow combine the two.

Any ideas.
Paul

 

[Eduardo]

Hi,
=sumproduct(--(A5=C:C))+sumproduct(--(E:E=5))+sumproduct(--(F:F=4))

 

[T. Valko]

=SUM(IF(FREQUENCY(IF(C:C=A5,D),D),1))

I assume you're using Excel 2007.

Try it like this:

=SUM(IF(FREQUENCY(IF((C:C=A5)*(E:E=5)*(F:F=4),D),D),1))

I'd use a smaller specific range if I were you! You're testing well over 3
million cells!

 

[PCLIVE]

This works the way I need. Thanks for the tip on the extensive ranges. I
did that quickly just as an example.

Thanks again,
Paul

--

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Shane Devenshire]

You need to test the results!

=SUM(IF(FREQUENCY(IF((C:C=A5)*(E:E=5)*(F:F=4),D),D),1))

This formula needs to be entered as an array, although you will get a result
if you don't it is very likely incorrect - it will be off by 2 in most cases,
with a large data set you won't notice that, but.... However, if you enter
it as an array in 2003 you it will return a #NUM! error because you are
referencing the entire column. In 2007 that won't happen.

Also, this formula may return a #VALUE! error if the entry in A5 is numeric
and some of the entries in C:C are text.

 

[T. Valko]

this formula may return a #VALUE! error if the
entry in A5 is numeric and some of the entries
in C:C are text

Hmmm....

I can't reproduce that result. I'll bet you forgot to array enter when you
tested that.

...........C.....D.....E.....F
1........x......1.....5......4
2........1......1.....5.....4
3........5......1.....5.....4
4........x......1.....5.....4
5........5......2.....5.....4

A5 = 5

Array entered:

=SUM(IF(FREQUENCY(IF((C1:C5=A5)*(E1:E5=5)*(F1:F5=4),D15),D15),1))

Returns the correct result of 2.

A5 = x

Returns the correct result of 1.