SUMPRUDUCT 3 Pieces of Criteria
 

OK - I admit it... I suck at SUMPRODUCT formulas!

Here is what I have now:

SUMMARY WORKSHEET:
A4:A9 = SiteName
B3:AF3 = Date
B4 = example SUMPRODUCT Formula as follows:

=SUMPRODUCT(--('Raw Data'!$D$1:$D$25000=($A4))*--('Raw
Data'!$H$1:$H$25000=(B$3)))

RAW DATA TAB:
COL.D = Site Names
COL.H = Dates
COL.U = AuditGroups (Values = "Floor Supervisor", "QA" and "Supervisor"
but are subject to change at any time)

My current formula correctly reports the number of records by site / by date.

- HOWEVER -

I now have to add a third piece of criteria... The data from Column-U in the
Raw Data worksheet.

So I need to lookup by Site (Col.D), by Date (Col.H) and only include
records where the "Audit Group" (Col.U) = "QA".

I have other changes to make as well, but the logic is the same - only the
criteria/sources will change.

Can this even be done with a SUMPRODUCT formula?

Thanks in advance!

--
"Trying to make reports so easy... even a monkey could run ''em!"

SUMPRUDUCT 3 Pieces of Criteria
 

Try this:

=SUMPRODUCT(--('Raw Data'!$D$1:$D$25000=$A4),--('Raw
Data'!$H$1:$H$25000=B$3),--('Raw Data'!$U$1:$U$25000="QA"))


--
Biff
Microsoft Excel MVP


"RayportingMonkey" wrote in
message ...
OK - I admit it... I suck at SUMPRODUCT formulas!

Here is what I have now:

SUMMARY WORKSHEET:
A4:A9 = SiteName
B3:AF3 = Date
B4 = example SUMPRODUCT Formula as follows:

=SUMPRODUCT(--('Raw Data'!$D$1:$D$25000=($A4))*--('Raw
Data'!$H$1:$H$25000=(B$3)))

RAW DATA TAB:
COL.D = Site Names
COL.H = Dates
COL.U = AuditGroups (Values = "Floor Supervisor", "QA" and "Supervisor"
but are subject to change at any time)

My current formula correctly reports the number of records by site / by
date.

- HOWEVER -

I now have to add a third piece of criteria... The data from Column-U in
the
Raw Data worksheet.

So I need to lookup by Site (Col.D), by Date (Col.H) and only include
records where the "Audit Group" (Col.U) = "QA".

I have other changes to make as well, but the logic is the same - only the
criteria/sources will change.

Can this even be done with a SUMPRODUCT formula?

Thanks in advance!

--
"Trying to make reports so easy... even a monkey could run ''em!"