Help required - SUMPRODUCT with wildcard search, multiple criteria on different sheets

[cageorge95]

To set the scene, I have an order report that has every order placed on the ordering system for every project ever. I want to add up the sum of the 'quantity ordered' and the 'price per unit' for a specific item and a specific site. Therefore, I need to only add the sum of 'quantity ordered' and 'price per unit' for the a specific site and item.

The equation I came up with is:

=SUMPRODUCT(('Orders (general)'!$F:$F="SCN1060")*(--(ISNUMBER(FIND($A27,'Orders (general)'!$G:$G))))*('Orders (general)'!$J:$J)*('Orders (general)'!$M:$M))

'Orders (general)' is the order sheet that is separate from the sheet I am doing this calculation on.

('Orders (general)'!$F:$F="SCN1060") is the the term to search the item code column for item SCN1060.

(--(ISNUMBER(FIND($A27,'Orders (general)'!$G:$G)))) is the term to wildcard search the 'project title' column for the project number (the report generates project titles in the format "[Project number] - [Project location]").

('Orders (general)'!$J:$J) is the quantity ordered column.

('Orders (general)'!$M:$M)) is the price per unit column.

My equation results in '#VALUE!'.

Can anyone tell me where I'm going wrong?

 

[KRice]

Yes, you want the logical checks to determine the correct row to be one argument and the other items to multiply together to be separate arguments:

=SUMPRODUCT(('Orders (general)'!$F:$F="SCN1060")*(ISNUMBER(FIND($A27,'Orders (general)'!$G:$G))),('Orders (general)'!$J:$J),('Orders (general)'!$M:$M))

And because the ISNUMBER function is multiplied by another array, you do not need to coerce it to 1's and 0's with the double unary operator...you can if desired, but that will happen automatically when multiplied by the other array. The product of those two arrays determines the correct row. As a side note, I would recommend changing the full column references to something conveniently large but not the full column to improve performance...perhaps e.g., $F2:$F1000 or something that covers your entire data set.

 

[cageorge95]

Yes, you want the logical checks to determine the correct row to be one argument and the other items to multiply together to be separate arguments:

=SUMPRODUCT(('Orders (general)'!$F:$F="SCN1060")*(ISNUMBER(FIND($A27,'Orders (general)'!$G:$G))),('Orders (general)'!$J:$J),('Orders (general)'!$M:$M))

And because the ISNUMBER function is multiplied by another array, you do not need to coerce it to 1's and 0's with the double unary operator...you can if desired, but that will happen automatically when multiplied by the other array. The product of those two arrays determines the correct row. As a side note, I would recommend changing the full column references to something conveniently large but not the full column to improve performance...perhaps e.g., $F2:$F1000 or something that covers your entire data set.

This worked a treat. Very informative, thank you!

 

[KRice]

I'm happy to help.