Home 
Search 
Today's Posts 
#1




Frequency of values with Criteria
Hi!
I need some help in coming up with a formula that counts the frequency of something that also matches a particular criteria. I have a list of people and the duration that they took to do something: Person Duration Pax A 10 Pax B 3 Pax A 6 Pax C 2 Pax B 7 Pax C 1 Pax A 9 And I need to calculate the frequency of each person's Duration within the range of 1 5 8 10 So the desired results a Range Person 1 5 8 10 Pax A 0 0 1 2 Pax B 0 1 1 0 Pax C 1 1 0 0 I'm using Excel 2002. Any help is greatly appreciated! Cheers Michelle 
#2




One play ..
Assume the source list is in Sheet1, cols A & B data from row2 down Person Duration Pax A 10 Pax B 3 Pax A 6 etc In an empty col to the right, say col D, Put in D2: =IF(OR(A2="",COUNTIF($A$2:A2,A2)1),"",ROW()) Copy D2 down to say, D100, to cover the max expected data (Leave D1 empty) In Sheet2  With the headers in A1:E1 : Person 1 5 8 10 Put in A2: =IF(ISERROR(SMALL(Sheet1!D:D,ROWS($A$1:A1))),"",IN DEX(Sheet1!A:A,MATCH(SMALL (Sheet1!D:D,ROWS($A$1:A1)),Sheet1!D:D,0))) Put in B2: =IF($A2="","",SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)* (Sheet1!$B$2:$B$100=B$1))) Put in C2: =IF($A2="","",SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)* (Sheet1!$B$2:$B$100B$1)*( Sheet1!$B$2:$B$100<=C$1))) Copy C2 across to E2 Then select A2:E2, copy down to E100 (cover the same range as per col D in Sheet1) The above will return the desired results: Person 1 5 8 10 Pax A 0 0 1 2 Pax B 0 1 1 0 Pax C 1 1 0 0 Adapt to suit ..  Rgds Max xl 97  Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895  "Michelle Wong" <Michelle wrote in message ... Hi! I need some help in coming up with a formula that counts the frequency of something that also matches a particular criteria. I have a list of people and the duration that they took to do something: Pax C 2 Pax B 7 Pax C 1 Pax A 9 And I need to calculate the frequency of each person's Duration within the range of 1 5 8 10 So the desired results a Range Person 1 5 8 10 Pax A 0 0 1 2 Pax B 0 1 1 0 Pax C 1 1 0 0 I'm using Excel 2002. Any help is greatly appreciated! Cheers Michelle 
#3




Hi Michelle,
I assume your data in A1: B8 and Bin array in F2:I2 (1,5,8,10) To return the Unique Person Name: Enter this array formula in E3: =IF(ROWS($E$2:E2)<=SUMPRODUCT(($A$2:$A$8<"")/COUNTIF($A$2:$A$8,$A$2:$A$8&"")),INDEX(A2:$A$8,MAT CH(0,COUNTIF($E$2:E2,A2:$A$8), 0)),"") Confirmed the formula by Ctrl + Shift + Enter and drag down. Copy this formula, =TRANSPOSE(FREQUENCY(IF($A$2:$A$8=$E3,$B$2:$B$8,"" ),$F$2:$I$2)) Select F3 till I3 and hit F2, and paste the formula by using Ctrl+V. Confirmed the formula by Ctrl + Shift + Enter and drag down It will return the result as needed. Attached is the sample file. http://savefile.com/files/8267872 Hope this help.  "Michelle Wong" <Michelle wrote in message ... Hi! I need some help in coming up with a formula that counts the frequency of something that also matches a particular criteria. I have a list of people and the duration that they took to do something: Person Duration Pax A 10 Pax B 3 Pax A 6 Pax C 2 Pax B 7 Pax C 1 Pax A 9 And I need to calculate the frequency of each person's Duration within the range of 1 5 8 10 So the desired results a Range Person 1 5 8 10 Pax A 0 0 1 2 Pax B 0 1 1 0 Pax C 1 1 0 0 I'm using Excel 2002. Any help is greatly appreciated! Cheers Michelle 
#4




Hi kk
Thanks a lot for your help! It works! :)) cheers Michelle "kk" wrote: Hi Michelle, I assume your data in A1: B8 and Bin array in F2:I2 (1,5,8,10) To return the Unique Person Name: Enter this array formula in E3: =IF(ROWS($E$2:E2)<=SUMPRODUCT(($A$2:$A$8<"")/COUNTIF($A$2:$A$8,$A$2:$A$8&"")),INDEX(A2:$A$8,MAT CH(0,COUNTIF($E$2:E2,A2:$A$8), 0)),"") Confirmed the formula by Ctrl + Shift + Enter and drag down. Copy this formula, =TRANSPOSE(FREQUENCY(IF($A$2:$A$8=$E3,$B$2:$B$8,"" ),$F$2:$I$2)) Select F3 till I3 and hit F2, and paste the formula by using Ctrl+V. Confirmed the formula by Ctrl + Shift + Enter and drag down It will return the result as needed. Attached is the sample file. http://savefile.com/files/8267872 
#5




Hi Michelle
You're welcome!. Thank for the feed back. kk "Michelle Wong" wrote in message ... Hi kk Thanks a lot for your help! It works! :)) cheers Michelle "kk" wrote: Hi Michelle, I assume your data in A1: B8 and Bin array in F2:I2 (1,5,8,10) To return the Unique Person Name: Enter this array formula in E3: =IF(ROWS($E$2:E2)<=SUMPRODUCT(($A$2:$A$8<"")/COUNTIF($A$2:$A$8,$A$2:$A$8&"")),INDEX(A2:$A$8,MAT CH(0,COUNTIF($E$2:E2,A2:$A$8), 0)),"") Confirmed the formula by Ctrl + Shift + Enter and drag down. Copy this formula, =TRANSPOSE(FREQUENCY(IF($A$2:$A$8=$E3,$B$2:$B$8,"" ),$F$2:$I$2)) Select F3 till I3 and hit F2, and paste the formula by using Ctrl+V. Confirmed the formula by Ctrl + Shift + Enter and drag down It will return the result as needed. Attached is the sample file. http://savefile.com/files/8267872 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Combine FREQUENCY and SUM of Associated Values  Excel Worksheet Functions  
How do I setup criteria in DSUM to sum values NOT equal (<>)  Excel Worksheet Functions  
how to sum highest ranking values meeting criteria within a limit?  Excel Discussion (Misc queries)  
Sum the values of one column, only if they meet certain criteria .  Excel Worksheet Functions  
Need formula to check values of data in several cells as criteria  Excel Worksheet Functions 