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

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

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.