Count With Multiple Criteria, please help!!

  • Thread starter Thread starter Kristina
  • Start date Start date
K

Kristina

I need some help with a formula in excel. Iwould like to be able to count
the number of times each Item code appears for each store. Here is the data
table:
A B
Store Item code
cyam01 10
cyam01 10
cyam01 20
cyam02 10
cyam02 20
edar01 10
edar02 10
tita01 20

Also, e.g. cyam is 1 store (made up of cyam01 & cyam02) etc.

I require the results in this table format:
cyam edar tita
10 3 ? ?
20 2 ? ?

"?" is the number of times each item code (10 and 20) occurred for each
store.
For example the result for the store CYAM: Item code 10 appeared 3 times &
item code 20 appeared twice.
There are also times that there isn't a certain item for a store, so in this
case I don't want any error messages just a zero (0).

Thank you in advance for your help.
 
Use SUMPRODUCT, Change the entries in text in the formula to the right
reference

=SUMPRODUCT(--($A2:$A9="cyam01"),--($B2:$B9=10))

If this post helps click Yes
 
with B12="cyam" (etc.) and A13=10 (etc.)

=SUMPRODUCT((LEFT($A$1:$A$8,4)=B$12)*($B$1:$B$8=$A13))

then copy/drag down and right

HIH
 
Thank you both very very much, both formula's are work perfectly!!! It's
just great that you take your time to help people out. In much appreciation,
regards from a relieved Australian!
 
Back
Top