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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

I should make the point that using INDEX in this context is regarded as Semi-Volatile in so far as it will act as though Volatile when the file is initially opened (ie will calculate) but will cease to be Volatile thereafter.

For more info. on Volatility and optimisation be sure to follow the link in my thread to Charles Williams' (invaluable) site ... well worth a read through as and when you have the time / inclination.