Count With Multiple Criteria, please help!!

[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.

 

[Jacob Skaria]

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

 

[Jarek Kujawa]

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

 

[Kristina]

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!