Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT QUERY
I am trying to provide some details on a daily changing set of data on sheet
GBP. The data table shows date (col A), customer ref (col D), amount (col H), hold reason & team responsible. I currently use the following to calculate the value of records dated 27Nov08 that have a blank customer ref: =SUMPRODUCT((GBP!A2:A60000=DATE(2008,11,27))*(GBP! H2:H60000)*(GBP!D2:D60000=" ")) I need to provide a count of items for a particular date where no customer ref exists that one team (Team A) is responsible for. And also the total value amount for these. Please help :o) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT QUERY
Untested, but I think something along these lines should do it ok
I assumed v.small ranges (do you really, really need those huge ranges?) and that "TeamA" is spelled out/found in col B (you didn't mention this) To get Count based on your multiple criteria: =SUMPRODUCT((GBP!A2:A60=DATE(2008,11,27))*(TRIM(GB P!D2:D60)="")*(GBP!B2:B60="TeamA")) To Sum col H Amts based on your multiple criteria: =SUMPRODUCT((GBP!A2:A60=DATE(2008,11,27))*(TRIM(GB P!D2:D60)="")*(GBP!B2:B60="TeamA"),GBP!H2:H60) -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- "jebreeg" wrote: I am trying to provide some details on a daily changing set of data on sheet GBP. The data table shows date (col A), customer ref (col D), amount (col H), hold reason & team responsible. I currently use the following to calculate the value of records dated 27Nov08 that have a blank customer ref: =SUMPRODUCT((GBP!A2:A60000=DATE(2008,11,27))*(GBP! H2:H60000)*(GBP!D2:D60000=" ")) I need to provide a count of items for a particular date where no customer ref exists that one team (Team A) is responsible for. And also the total value amount for these. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT query | Excel Discussion (Misc queries) | |||
Sumproduct query | Excel Discussion (Misc queries) | |||
SUMPRODUCT Query | Excel Discussion (Misc queries) | |||
Sumproduct Query | Excel Discussion (Misc queries) | |||
I think its a sumproduct query? | Excel Discussion (Misc queries) |