Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.worksheet.functions




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 
#2




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:
Let me explain how this formula works:
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 
#3
Posted to microsoft.public.excel.worksheet.functions




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 
#4
Posted to microsoft.public.excel.worksheet.functions




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 
#5
Posted to microsoft.public.excel.worksheet.functions




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 
#6
Posted to microsoft.public.excel.worksheet.functions




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 
#7
Posted to microsoft.public.excel.worksheet.functions




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 
#8
Posted to microsoft.public.excel.worksheet.functions




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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Calculate FREQUENCY based on criteria...  Excel Discussion (Misc queries)  
counting frequency using multiple ranges/criteria  Excel Worksheet Functions  
Frequency of data with criteria  Excel Worksheet Functions  
frequency formula with criteria  Excel Worksheet Functions  
Frequency of values with Criteria  Excel Worksheet Functions 