Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count/Sum based on mutiple criteria
I could not find help on this one. I have the spreadsheet below that I want
to do this: Count the number of times that an SC1 has Rev between 250 and 550. Then I would also like to sum up (in a different column) the rev for the same criteria. This should be easy with sumproduct but I cannot get it done. Any help? Name Division Rev Tom SC1 0 fish SC3 0 roger SC2 300 steve SC2 900 cindy SC1 650 kim SC1 1100 ryan SC3 210 bill SC1 1200 tony SC1 400 ted SC2 200 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count/Sum based on mutiple criteria
To count:
=SUMPRODUCT((B2:B11="SC1")*(C2:C11=250)*(C2:C11<= 550)) To total: =SUMPRODUCT((B2:B11="SC1")*(C2:C11=250)*(C2:C11<= 550)*C2:C11) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Jon Dow" wrote in message ... I could not find help on this one. I have the spreadsheet below that I want to do this: Count the number of times that an SC1 has Rev between 250 and 550. Then I would also like to sum up (in a different column) the rev for the same criteria. This should be easy with sumproduct but I cannot get it done. Any help? Name Division Rev Tom SC1 0 fish SC3 0 roger SC2 300 steve SC2 900 cindy SC1 650 kim SC1 1100 ryan SC3 210 bill SC1 1200 tony SC1 400 ted SC2 200 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count/Sum based on mutiple criteria
Try this:
=SUMPRODUCT((B2:B10="SC1")*(C2:C10=250)*(C2:C10<= 550)) for the count, and this one for the sum: =SUMPRODUCT((B2:B10="SC1")*(C2:C10=250)*(C2:C10<= 550)*(C2:C10)) Change the ranges to suit. Hope this helps. Pete On Sep 16, 4:31*pm, Jon Dow wrote: I could not find help on this one. I have the spreadsheet below that I want to do this: Count the number of times that an SC1 has Rev between 250 and 550. Then I would also like to sum up (in a different column) the rev for the same criteria. This should be easy with sumproduct but I cannot get it done. Any help? Name * *Division * * * *Rev Tom * * SC1 * * 0 fish * *SC3 * * 0 roger * SC2 * * 300 steve * SC2 * * 900 cindy * SC1 * * 650 kim * * SC1 * * 1100 ryan * *SC3 * * 210 bill * *SC1 * * 1200 tony * *SC1 * * 400 ted * * SC2 * * 200 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count/Sum based on mutiple criteria
This board rocks!! It never lets me down. Thanks for your help
"Jon Dow" wrote: I could not find help on this one. I have the spreadsheet below that I want to do this: Count the number of times that an SC1 has Rev between 250 and 550. Then I would also like to sum up (in a different column) the rev for the same criteria. This should be easy with sumproduct but I cannot get it done. Any help? Name Division Rev Tom SC1 0 fish SC3 0 roger SC2 300 steve SC2 900 cindy SC1 650 kim SC1 1100 ryan SC3 210 bill SC1 1200 tony SC1 400 ted SC2 200 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count/Sum based on mutiple criteria
We appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Jon Dow" wrote in message ... This board rocks!! It never lets me down. Thanks for your help "Jon Dow" wrote: I could not find help on this one. I have the spreadsheet below that I want to do this: Count the number of times that an SC1 has Rev between 250 and 550. Then I would also like to sum up (in a different column) the rev for the same criteria. This should be easy with sumproduct but I cannot get it done. Any help? Name Division Rev Tom SC1 0 fish SC3 0 roger SC2 300 steve SC2 900 cindy SC1 650 kim SC1 1100 ryan SC3 210 bill SC1 1200 tony SC1 400 ted SC2 200 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNT based on 2 criteria | Excel Worksheet Functions | |||
Count based on criteria | Excel Discussion (Misc queries) | |||
Count distinct based on criteria | Excel Worksheet Functions | |||
Count Cells Mutiple Criteria | Excel Discussion (Misc queries) | |||
Count or Sum based on more than 1 criteria | Excel Worksheet Functions |