Count With Multiple Criteria, please help!!
 

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.

Count With Multiple Criteria, please 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
---------------
Jacob Skaria


"Kristina" wrote:

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.

Count With Multiple Criteria, please help!!
 

with B12="cyam" (etc.) and A13=10 (etc.)

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

then copy/drag down and right

HIH


On 21 Kwi, 06:43, Kristina wrote:
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. * *

Count With Multiple Criteria, please help!!
 

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!

"Jarek Kujawa" wrote:

with B12="cyam" (etc.) and A13=10 (etc.)

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

then copy/drag down and right

HIH


On 21 Kwi, 06:43, Kristina wrote:
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.