SUMPRODUCT Macro, Excel 2007 Help

Hello excel experts,
I was wondering if someone could point me in the right direction on an issue I am having with an excel formula.

=SUMPRODUCT(--(INDIRECT("Data!A2:A1000")=A8),
--(INDIRECT("Data!B2:B1000")="Late load -  More than 2 hours late unless first stop")
+(INDIRECT("Data!C2:C1000")="2 hrs")
+(INDIRECT("Data!C2:C1000")="4 hrs")
+(INDIRECT("Data!C2:C1000")="6 hrs"))

Here is some sample data that I want to select in the above formula:
TestCompany, Late load - More than 2 hours late unless first stop, 2hrs
TestCompany, Late load - More than 2 hours late unless first stop, 4hrs
TestCompany, Late load - More than 2 hours late unless first stop, 6hrs

This however is also getting selected:
TestCompany, AnyValueHere, 2hrs

For lack of a better way of describing it I’ll use SQL syntax:

SELECT column1,column2,column3
FROM MySpreadsheet
WHERE column1='TestCompany' AND column2='More than 2 hours late unless first stop' AND 
column3 IN("2 hrs","4 hrs","6 hrs”)

Any help would be greatly appreciated.
Cheers!

You need to be careful when conducting ORs in Arrays / Sumproducts by virtue of addition...

Either:

=SUMPRODUCT(--(Data!A2:A1000=A8),--(Data!B2:B1000="Late load - More than 2 hours late unless first stop"),--ISNUMBER(SEARCH({"2 hrs","4 hrs","6 hrs"},Data!C2:C1000)))

or

=SUMPRODUCT(--(Data!A2:A1000=A8),--(Data!B2:B1000="Late load - More than 2 hours late unless first stop"),(Data!C2:C1000="2 hrs")+(Data!C2:C1000="4 hrs")+(Data!C2:C1000="6 hrs"))

The problem you have with the existing approach is that your text test in B is being used as part of the OR test, ie:

(B=text)+(C=x)+(C=y)+(C=z)

meaning that should C = x/y/z then irrespective of whether or not B=text the row will be counted.... you would also be at risk of double counting the row wherever B=text and C = x/y/z given you would end up with a multiple of 2 rather than 1.

On an aside... there's no need for INDIRECT given the sheet reference is fixed - using it will make the SUMPRODUCT Volatile and generally speaking Volatile Arrays / Sumproducts are best avoided whenever/wherever possible.

Originally Posted by DonkeyOte

=SUMPRODUCT(--(Data!A2:A1000=A8),--(Data!B2:B1000="Late load - More than 2 hours late unless first stop"),(Data!C2:C1000="2 hrs")+(Data!C2:C1000="4 hrs")+(Data!C2:C1000="6 hrs"))

Thank-you very much your solution is 100% on the mark. The reason I was using the INDIRECT function is because the record set, on the "data" worksheet, is perodically deleted causing the #REF error to appear in my formulas, in place of the referenced cells, when the workbook automatically recalculated.

Again thank-you for such a well explained solution!

No need to quote prior posts in your reply - simply clutters your thread.

Originally Posted by edgewild

The reason I was using the INDIRECT function is because the record set, on the "data" worksheet, is perodically deleted causing the #REF error to appear in my formulas, in place of the referenced cells, when the workbook automatically recalculated.

If the "Data" sheet itself is not physically deleted you can still avoid the Volatility by virtue of INDEX

=SUMPRODUCT(--(INDEX(Data!$1:$65536,2,1):INDEX(Data!$1:$65536,1000,1)=A8),--(INDEX(Data!$1:$65536,2,2):INDEX(Data!$1:$65536,1000,2)="Late load - More than 2 hours late unless first stop"),--ISNUMBER(SEARCH({"2 hrs","4 hrs","6 hrs"},INDEX(Data!$1:$65536,2,3):INDEX(Data!$1:$65536,1000,3))))

As long as the sheet itself is never physically removed the above will continue to reference whatever ends up in A2:C1000 regardless of row/column deletions etc.