Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif, multiple criteria...
I have a table like this:
(sorry if the message mangles it, but there are tabs so you can paste it correctly if you need to... I think) Store Category Item A 1 blahblah A 1 blahblah B 1 blahblah B 1 blahblah A 2 blahblah A 2 blahblah A 2 blahblah A 2 blahblah A 2 blahblah B 2 blahblah A 3 blahblah B 3 blahblah C 3 blahblah I'd like to use a countif formula to generate the occurrences column. Ideas? Results Location Category Number of Occurrences A 1 2 2 5 3 1 B 1 2 2 1 3 1 C 1 0 2 0 3 1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif, multiple criteria...
SumProduct should do it. Here are some notes I have on it's use. [your
specific example should leave out the 3rd array in the SumProduct formula. '/-----------------------------------------------------------/ 'DATA: ' A B C '1 Start stop projection '2 0 78 1 '3 79 200 2 '4 201 500 3 '5 501 1000 4 '/-----------------------------------------------------------/ 'FORMULAS: ' A B ' 7 Score Projection using SumProduct formula ' 8 400 3 =SUMPRODUCT(--(A8=A2:A5),--(A8<=B2:B5),--(C2:C5)) ' 9 15 1 =SUMPRODUCT(--(A8=A2:A5),--(A8<=B2:B5),--(C2:C5)) '10 799 4 =SUMPRODUCT(--(A10=A2:A5),--(A10<=B2:B5),--(C2:C5)) '11 82 2 =SUMPRODUCT(--(A11=A2:A5),--(A11<=B2:B5),--(C2:C5)) '/-----------------------------------------------------------/ 'SumProduct muliplies the 1st array * 2nd array * 3rd array * etc ' and then adds the products ' 'FALSE = 0 / TRUE = 1 ' 'In the DATA above, SumProduct evaluates the 400 in Cell A8 as 'FALSE * FALSE * 1 'FALSE * FALSE * 2 'TRUE * TRUE * 3 'FALSE * FALSE * 4 ' 'OR ' ' 0 * 0 * 1 = 0 ' 0 * 0 * 2 = 0 ' 1 * 1 * 3 = 3 ' 0 * 0 * 4 = 0 ' --- ' 3 ' === '/-----------------------------------------------------------/ 'NOTES: ' The double dash "--" is used because you may be summing a range 'that could contain non-numeric text or Booleans. This will coerce 'the returns to 0 or 1. Otherwise, #VALUE! may be returned. '/-----------------------------------------------------------/ HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Oggie Ben Doggie" wrote: I have a table like this: (sorry if the message mangles it, but there are tabs so you can paste it correctly if you need to... I think) Store Category Item A 1 blahblah A 1 blahblah B 1 blahblah B 1 blahblah A 2 blahblah A 2 blahblah A 2 blahblah A 2 blahblah A 2 blahblah B 2 blahblah A 3 blahblah B 3 blahblah C 3 blahblah I'd like to use a countif formula to generate the occurrences column. Ideas? Results Location Category Number of Occurrences A 1 2 2 5 3 1 B 1 2 2 1 3 1 C 1 0 2 0 3 1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif, multiple criteria...
If you want the result formatted as shown, use a Pivot table. Generate it by
selecting it from the Data menu and following the prompts. Otherwise you can use a formula to count the occurrences (assumes 'Store' is in A1 and 'Category' is in B1) =sumproduct(--(a2:a1000="A") , --(B2:b1000=1)) Change the ="A" and the =1 to reflect your actual values "Oggie Ben Doggie" wrote: I have a table like this: (sorry if the message mangles it, but there are tabs so you can paste it correctly if you need to... I think) Store Category Item A 1 blahblah A 1 blahblah B 1 blahblah B 1 blahblah A 2 blahblah A 2 blahblah A 2 blahblah A 2 blahblah A 2 blahblah B 2 blahblah A 3 blahblah B 3 blahblah C 3 blahblah I'd like to use a countif formula to generate the occurrences column. Ideas? Results Location Category Number of Occurrences A 1 2 2 5 3 1 B 1 2 2 1 3 1 C 1 0 2 0 3 1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Countif with multiple criteria in the same column. | Excel Discussion (Misc queries) | |||
countif using multiple criteria | Excel Discussion (Misc queries) | |||
COUNTIF or SUM function (Multiple criteria) HELP!! | Excel Worksheet Functions | |||
countif function with multiple criteria | Excel Discussion (Misc queries) | |||
Countif w/ Multiple Criteria | Excel Worksheet Functions |