software

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)

StoreCategoryItem
A1blahblah
A1blahblah
B1blahblah
B1blahblah
A2blahblah
A2blahblah
A2blahblah
A2blahblah
A2blahblah
B2blahblah
A3blahblah
B3blahblah
C3blahblah

I'd like to use a countif formula to generate the occurrences column.
Ideas?

Results
LocationCategoryNumber of Occurrences
A12
25
31
B12
21
31
C10
20
31SumProduct 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(--(A8gt;=A2:A5),--(A8lt;=B2:B5),--(C2:C5))
' 9 15 1 =SUMPRODUCT(--(A8gt;=A2:A5),--(A8lt;=B2:B5),--(C2:C5))
'10 799 4 =SUMPRODUCT(--(A10gt;=A2:A5),--(A10lt;=B2:B5),--(C2:C5))
'11 82 2 =SUMPRODUCT(--(A11gt;=A2:A5),--(A11lt;=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 quot;--quot; 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?''.quot;Oggie Ben Doggiequot; wrote:

gt; I have a table like this:
gt; (sorry if the message mangles it, but there are tabs so you can paste
gt; it correctly if you need to... I think)
gt;
gt; StoreCategoryItem
gt; A1blahblah
gt; A1blahblah
gt; B1blahblah
gt; B1blahblah
gt; A2blahblah
gt; A2blahblah
gt; A2blahblah
gt; A2blahblah
gt; A2blahblah
gt; B2blahblah
gt; A3blahblah
gt; B3blahblah
gt; C3blahblah
gt;
gt; I'd like to use a countif formula to generate the occurrences column.
gt; Ideas?
gt;
gt; Results
gt; LocationCategoryNumber of Occurrences
gt; A12
gt; 25
gt; 31
gt; B12
gt; 21
gt; 31
gt; C10
gt; 20
gt; 31
gt;
gt;

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=quot;Aquot;) , --(B2:b1000=1))

Change the =quot;Aquot; and the =1 to reflect your actual values
quot;Oggie Ben Doggiequot; wrote:

gt; I have a table like this:
gt; (sorry if the message mangles it, but there are tabs so you can paste
gt; it correctly if you need to... I think)
gt;
gt; StoreCategoryItem
gt; A1blahblah
gt; A1blahblah
gt; B1blahblah
gt; B1blahblah
gt; A2blahblah
gt; A2blahblah
gt; A2blahblah
gt; A2blahblah
gt; A2blahblah
gt; B2blahblah
gt; A3blahblah
gt; B3blahblah
gt; C3blahblah
gt;
gt; I'd like to use a countif formula to generate the occurrences column.
gt; Ideas?
gt;
gt; Results
gt; LocationCategoryNumber of Occurrences
gt; A12
gt; 25
gt; 31
gt; B12
gt; 21
gt; 31
gt; C10
gt; 20
gt; 31
gt;
gt;