Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT based on 2 criteria
I have the formula below that sums the values in Range name
'Purchases' based on 2 criteria. How could I modify this to COUNT the number of instances of receipts, based on the existing 2 criteria? =(SUMPRODUCT((Store=123)*(Inventory_No=$I$6),(Purc hases))/10) I've tried =(COUNT((StoreNo=123)*(Inventory_No=$I$6),(Purchas es))/10) and =COUNT((SUMPRODUCT((StoreNo=123)*(Inventory_No=$I$ 6),(Purchases))/ 10)) But they don't give me the correct answer |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT based on 2 criteria
=SUMPRODUCT(--(Store=123),--(Inventory_No=$I$6))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... I have the formula below that sums the values in Range name 'Purchases' based on 2 criteria. How could I modify this to COUNT the number of instances of receipts, based on the existing 2 criteria? =(SUMPRODUCT((Store=123)*(Inventory_No=$I$6),(Purc hases))/10) I've tried =(COUNT((StoreNo=123)*(Inventory_No=$I$6),(Purchas es))/10) and =COUNT((SUMPRODUCT((StoreNo=123)*(Inventory_No=$I$ 6),(Purchases))/ 10)) But they don't give me the correct answer |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT based on 2 criteria
Thank you, but how would that count the number of purchases? (I've
other columns of data in my sheet also) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT based on 2 criteria
Try it and see.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Thank you, but how would that count the number of purchases? (I've other columns of data in my sheet also) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT based on 2 criteria
Just tried that, answer = 7, but should be 2
If I have 10 columns of data and the range name 'Purchases' is in Column F, how would the formula count the number of receipts, for Store 123 and Inventory_No in I6, or am I missing something? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT based on 2 criteria
That is how that SP formula works. without seeing your data I cannot say why
you get 7 or why you think you should get 2, but assuming Store is column F, and assuming the formula that you posted woks, this is the same, it just doesn't multiply by the number of purchases or divide by 10. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Just tried that, answer = 7, but should be 2 If I have 10 columns of data and the range name 'Purchases' is in Column F, how would the formula count the number of receipts, for Store 123 and Inventory_No in I6, or am I missing something? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count based on criteria | Excel Discussion (Misc queries) | |||
Count based on multiple criteria | Excel Worksheet Functions | |||
Need to count based on different criteria in two columns | Excel Worksheet Functions | |||
Count distinct based on criteria | Excel Worksheet Functions | |||
Count or Sum based on more than 1 criteria | Excel Worksheet Functions |