Can the function countif be used as a subtotal?

S

Steve Romley

I have a database that lists sales information by region/store/item
a/item b

I want to count the number of stores by region that carry item a, and
compare that to the number of stores in the region that carry item b.
I'm not very good at excel, but there must be a way to do this.

Thanks
 
J

JE McGimpsey

I have a database that lists sales information by region/store/item
a/item b

I want to count the number of stores by region that carry item a, and
compare that to the number of stores in the region that carry item b.
I'm not very good at excel, but there must be a way to do this.

Thanks
Click to expand...

It sounds more like you'll want SUMPRODUCT().

Assume Region in column A, Store in column B, Item a in column C and
Item b in column D. Then the number of stores in region 1 that carry
item a is calculated with

=SUMPRODUCT(--(A1:A1000=1),--(C1:C1000>0))

and the number of items in region 3 that carry item b is

=SUMPRODUCT(--(A1:A1000=3),--(D1:D1000>0))

If instead of a number, items a and b are indicated with, say an "X" in
the column:

=SUMPRODUCT(--(A1:A1000=1),--(C1:C1000="X"))


For more flexibility, you may want to use a Pivot Table instead. See
Help and

http://peltiertech.com/Excel/Pivots/pivotstart.htm

for more info on PTs.
 
Top