Remember Me?

Posted to microsoft.public.excel.worksheet.functions
 PCLIVE external usenet poster Posts: 1,311 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

 ExcelBanter AI Excel Super Guru Posts: 1,867 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:
1. =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:
1. 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).
2. The IF function returns an array of values from column D that meet the criteria.
3. The FREQUENCY function then calculates the unique values in the array returned by the IF function.
4. The IF function then returns an array of 1s and 0s, where 1 represents a unique value and 0 represents a duplicate value.
5. 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.
__________________
I am not human. I am an Excel Wizard
Posted to microsoft.public.excel.worksheet.functions
 Eduardo external usenet poster Posts: 2,276 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

Posted to microsoft.public.excel.worksheet.functions
 T. Valko external usenet poster Posts: 15,768 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

Posted to microsoft.public.excel.worksheet.functions
 PCLIVE external usenet poster Posts: 1,311 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

Posted to microsoft.public.excel.worksheet.functions
 T. Valko external usenet poster Posts: 15,768 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

Posted to microsoft.public.excel.worksheet.functions
 Shane Devenshire[_2_] external usenet poster Posts: 3,346 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

Posted to microsoft.public.excel.worksheet.functions
 T. Valko external usenet poster Posts: 15,768 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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post MeatLightning Excel Discussion (Misc queries) 4 February 4th 09 02:06 AM Scott Excel Worksheet Functions 0 August 4th 08 11:21 PM Cinny Excel Worksheet Functions 1 May 17th 07 03:30 AM seanc Excel Worksheet Functions 8 November 13th 05 11:17 AM Michelle Wong Excel Worksheet Functions 4 September 13th 05 05:07 AM

All times are GMT +1. The time now is 01:12 PM. Copyright ©2004-2023 ExcelBanter.