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)

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.