Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT Help
I have a spreadsheet with multiple worksheets.
The worksheets relevant to my SUMPRODUCT problem are as follows:- Daily Report 2006 - this holds all the core data NW Customer Summary - this holds a weekly summary of issues occurring during a specific week, along with whether they are Haul, Prem, Cust or Other. EA Customer Summary - this is the same as the NW. Both the NW and EA Customer Summaries information comes from the Daily Report, where a vlookup has been established indicating whether it is EA or NW, along with whether it is Haul, Prem, Cust or Other. I have put together the following formula, but have so far been unsuccessful, so am unsure as to whether I have put too much information in, not looking at areas in the right order etc. Could anyone help me with this, please. I have not put this into the live document, so any reference of Sheet2 is referring to my example of EA Customer Summary to see if I could get it to work. =SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=Sheet2!$A$3),--('Daily Report 2006'!$B$2:$B$336<Sheet2!$A$3+7)--'Daily Report 2006'!$L$2:$L$336=$B$1)*('Daily Report 2006'!$M$2:$M$336=Sheet2!$D$2)*('Daily Report 2006'!$F$2:$F$336=Sheet2!$B$3) Sheet2 Cell A3 refers to the w/c date, for which I only require a week's worth of data in each element. It is looking at B2:B336 on Sheet2 for each date instance. Daily Report 2006 L2:L336 refers to whether the occurrence was either EA or NW, also as per a separate vlookup. Sheet2 B1 is in the hidden column/cell to dictate whether the summary is for the NW or EA. Daily Report 2006 M2:M336 refers to Haul, Prem, Cust or Other from vlookup option in the report with D2 in Sheet2 referring to the Haul, Prem, Cust or Other heading in that week's section. Also in the hidden column on Sheet2 I have put the short name customers, i.e. Asda, JS, Morr, Tesco and Other. Each customer has a row with each customer having an entry for Haul, Prem, Cust or Other and can some times have no entries in a week and others a variety. Column F in the Daily Report 2006 reflects the shortened version of the customer name/location for which I am trying to get the information to come together. Example of document I am trying to establish this formula into is as follows:- EA 04-Sep Haul Prem Cust Other Asda JS Tesco Morr Other Total Many thanks in advance. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
Need help with sumproduct & dynamic ranges | Excel Worksheet Functions | |||
Need help with sumproduct and dynamic ranges | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |