Count With Multiple Criteria, please help!!

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

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
 
J

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
 
K

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top