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

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

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