ExcelBanter - FREQUENCY with multiple criteria

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - FREQUENCY with multiple criteria (https://www.excelbanter.com/excel-worksheet-functions/235448-frequency-multiple-criteria.html)

FREQUENCY with multiple criteria

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

Answer: FREQUENCY with multiple criteria

Hi Paul,

To include additional criteria in the FREQUENCY function, you can use the SUMPRODUCT function along with the FREQUENCY function. Here's how you can modify your formula to include the additional criteria:

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

Let me explain how this formula works:

The IF function checks for the rows where cells in column C equal A5 (the first criteria), and cells in column E equal 5 and cells in column F equal 4 (the additional criteria).
The IF function returns an array of values from column D that meet the criteria.
The FREQUENCY function then calculates the unique values in the array returned by the IF function.
The IF function then returns an array of 1s and 0s, where 1 represents a unique value and 0 represents a duplicate value.
Finally, the SUM function adds up the 1s in the array to give you the count of unique values that meet all the criteria.

Note that this is an array formula, so you need to press Ctrl+Shift+Enter instead of just Enter to enter the formula.

FREQUENCY with multiple criteria

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

"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

FREQUENCY with multiple criteria

=SUM(IF(FREQUENCY(IF(C:C=A5,D:D),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) ,D:D),1))

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

--
Biff
Microsoft Excel MVP

"PCLIVE" wrote in message
...
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

FREQUENCY with multiple criteria

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" wrote in message
...
=SUM(IF(FREQUENCY(IF(C:C=A5,D:D),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) ,D:D),1))

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

--
Biff
Microsoft Excel MVP

"PCLIVE" wrote in message
...
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

FREQUENCY with multiple criteria

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"PCLIVE" wrote in message
...
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" wrote in message
...
=SUM(IF(FREQUENCY(IF(C:C=A5,D:D),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) ,D:D),1))

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

--
Biff
Microsoft Excel MVP

"PCLIVE" wrote in message
...
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

FREQUENCY with multiple criteria

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

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