Count Occurences
 

I have a soreadsheet with several worksheets. I would like to count the
numbers in Column E against the products in column B on worksheet labelled
BAGS and display the result in worksheet labelled SNAPSHOT.

My current formula is =SUMPRODUCT(A2=BAGS!$B$1:$B$6360)*(BAGS!$E$1:$E$63 60),
but this returns a value of zero.

Data on BAGS worksheet looks like this:-

DATE PRODUCT SIZE SHIFT No. OF BAGS SCRAPPED
21/06/07 Dri Coat 25Kg Day 2
21/06/07 Multi Finish 25Kg Day 3
21/06/07 Board Finish25Kg Day 2
21/06/07 Hard Wall 25Kg Day 1

Worksheet labelled SNAPSHOT looks like this :-

PRODUCT BAGS LOST
Board Finish 0
Bonding Coat 0
Dri Coat 0
Dri Wall Adhesive 0
Hard Wall 0
Multi Finish 0
Tough Coat 0

Any ideas? I expect to have sevral occurences of a product type and need
the total of bags lost in a time period.

Thanks

Count Occurences
 

You've missed a pair of brackets - try this:

=SUMPRODUCT((A2=BAGS!$B$1:$B$6360)*(BAGS!$E$1:$E$6 360))

Hope this helps.

Pete


On Jul 11, 11:10 am, Lmurraz
wrote:
I have a soreadsheet with several worksheets. I would like to count the
numbers in Column E against the products in column B on worksheet labelled
BAGS and display the result in worksheet labelled SNAPSHOT.

My current formula is =SUMPRODUCT(A2=BAGS!$B$1:$B$6360)*(BAGS!$E$1:$E$63 60),
but this returns a value of zero.

Data on BAGS worksheet looks like this:-

DATE PRODUCT SIZE SHIFT No. OF BAGS SCRAPPED
21/06/07 Dri Coat 25Kg Day 2
21/06/07 Multi Finish 25Kg Day 3
21/06/07 Board Finish25Kg Day 2
21/06/07 Hard Wall 25Kg Day 1

Worksheet labelled SNAPSHOT looks like this :-

PRODUCT BAGS LOST
Board Finish 0
Bonding Coat 0
Dri Coat 0
Dri Wall Adhesive 0
Hard Wall 0
Multi Finish 0
Tough Coat 0

Any ideas? I expect to have sevral occurences of a product type and need
the total of bags lost in a time period.

Thanks

Count Occurences
 

Try:

=SUMPRODUCT((A2=Bags!$B$2:$B$6360)*(Bags!$E$2:$E$6 360) )

"Lmurraz" wrote:

I have a soreadsheet with several worksheets. I would like to count the
numbers in Column E against the products in column B on worksheet labelled
BAGS and display the result in worksheet labelled SNAPSHOT.

My current formula is =SUMPRODUCT(A2=BAGS!$B$1:$B$6360)*(BAGS!$E$1:$E$63 60),
but this returns a value of zero.

Data on BAGS worksheet looks like this:-

DATE PRODUCT SIZE SHIFT No. OF BAGS SCRAPPED
21/06/07 Dri Coat 25Kg Day 2
21/06/07 Multi Finish 25Kg Day 3
21/06/07 Board Finish25Kg Day 2
21/06/07 Hard Wall 25Kg Day 1

Worksheet labelled SNAPSHOT looks like this :-

PRODUCT BAGS LOST
Board Finish 0
Bonding Coat 0
Dri Coat 0
Dri Wall Adhesive 0
Hard Wall 0
Multi Finish 0
Tough Coat 0

Any ideas? I expect to have sevral occurences of a product type and need
the total of bags lost in a time period.

Thanks

Count Occurences
 

=SUMIF( Bags!$B$2:$B$6360, a2, Bags!$E$2:$E$6360 )


--
Regards,
Luc.

"Festina Lente"


"Lmurraz" wrote:

I have a soreadsheet with several worksheets. I would like to count the
numbers in Column E against the products in column B on worksheet labelled
BAGS and display the result in worksheet labelled SNAPSHOT.

My current formula is =SUMPRODUCT(A2=BAGS!$B$1:$B$6360)*(BAGS!$E$1:$E$63 60),
but this returns a value of zero.

Data on BAGS worksheet looks like this:-

DATE PRODUCT SIZE SHIFT No. OF BAGS SCRAPPED
21/06/07 Dri Coat 25Kg Day 2
21/06/07 Multi Finish 25Kg Day 3
21/06/07 Board Finish25Kg Day 2
21/06/07 Hard Wall 25Kg Day 1

Worksheet labelled SNAPSHOT looks like this :-

PRODUCT BAGS LOST
Board Finish 0
Bonding Coat 0
Dri Coat 0
Dri Wall Adhesive 0
Hard Wall 0
Multi Finish 0
Tough Coat 0

Any ideas? I expect to have sevral occurences of a product type and need
the total of bags lost in a time period.

Thanks