FREQUENCY with multiple criteria
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 ),D1:D5),D1:D5),1))
Returns the correct result of 2.
A5 = x
Returns the correct result of 1.
--
Biff
Microsoft Excel MVP
"Shane Devenshire" wrote in
message ...
You need to test the results!
=SUM(IF(FREQUENCY(IF((C:C=A5)*(E:E=5)*(F:F=4),D:D) ,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.
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire
"PCLIVE" wrote:
I'd like to apply some additional criteria to this unique value count.
=SUM(IF(FREQUENCY(IF(C:C=A5,D:D),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
|