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