Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Occurences | Excel Worksheet Functions | |||
Count # of Occurences | Excel Discussion (Misc queries) | |||
Count Occurences problem | Excel Discussion (Misc queries) | |||
Trying to count occurences if 2 conditions are met | Excel Worksheet Functions | |||
Count occurences between dates | Excel Worksheet Functions |