Array Formula with Conditions Not Working

I'm having issues trying to get an array formula to work properly.

Simplified explanation: I'm working with a sheet of data (Orders) for work orders, with columns for workorder number, description, charge type, amount, etc. There can be multiple entries (charges) for a single workorder, for example charge type of "Parts" on one row, "Labor" on another row, "Refund" on another row, etc. There could also be several rows (for the same workorder) that are all for "Parts". On a separate Summary sheet, I need to sum, for each workorder, total parts, total labor, etc.

This would be simple, EXCEPT for that fact that sometimes the workorder number is MISSING from its column in the Orders sheet. But fortunately, the description field usually contains the workorder number (interspersed with other text). My client therefore wants me to also sum (for example) the total costs for "Parts" where the workorder column is blank, but the target workorder number (123456, for example) appears anywhere in the description.

There is a range name (such as Orders_Amounts) assigned to each column in the Orders data sheet. For starters, I wrote an array formula to simply sum ALL the amounts where the target workorder number (in cell A2 of my Summary sheet) appears anywhere in the description:

Please Login or Register  to view this content.

This does indeed give me a correct total. However, I then tried to limit the sum to only include "Parts":

Please Login or Register  to view this content.

This now simply gives me a ZERO result (although I've confirmed that every single row with the target workorder number does indeed show "Parts" under charge type).
Each of the named ranges is for a complete column, so the rows of each are equal. What am I doing wrong?

So you have 3 columns, in which 2 columns are criteria, 1 column is Amount, time together row by row, then sum all.
1st criteria range:
ISNUMBER(SEARCH(TEXT($A2,"0"),Orders_Descriptions)) return 1/0 if match/dont match
2nd criteria range
(Orders_ChargeTypes="Parts") return 1/0 if match/dont match

Then using SUMPRODUCT to sum all rows those =1*1*Amount

Please Login or Register  to view this content.

Sorry, that doesn't work. I still get a zero result. Even using SUMPRODUCT to get the sum of ALL entries (where description contains the workorder number):

Please Login or Register  to view this content.

doesn't work (result is zero). So this is worse than my original array formula.

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do with the formula.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Originally Posted by Merf

=SUMPRODUCT(ISNUMBER(SEARCH(TEXT($A2,"0"),Orders_Descriptions)),Orders_Amounts)
doesn't work (result is zero). So this is worse than my original array formula.

Not really, though it's no more efficient... in the above you need to coerce your Boolean array either discretely, or via multiplication with second array, i.e.:

=SUMPRODUCT(ISNUMBER(SEARCH(TEXT($A2,"0"),Orders_Descriptions))+0,Orders_Amounts)
or
=SUMPRODUCT(ISNUMBER(SEARCH(TEXT($A2,"0"),Orders_Descriptions))*Orders_Amounts)

So, if (either of) the above generate the same result as your initial array and yet, with extra condition, the below generates 0:

=SUMPRODUCT(ISNUMBER(SEARCH(TEXT($A2,"0"),Orders_Descriptions))*(Orders_ChargeTypes="Parts"),Orders_Amounts)

then this tells us that there are no values ="Parts"

you could then apply a TRIM to see if that's the problem (i.e. leading/trailing spaces)

=SUMPRODUCT(ISNUMBER(SEARCH(TEXT($A2,"0"),Orders_Descriptions))*(TRIM(Orders_ChargeTypes)="Parts"),Orders_Amounts)

or, consider another SEARCH - mindful of fact you could get false positives, at which point I would manipulate the strings accordingly, ie..

=SUMPRODUCT(ISNUMBER(SEARCH(TEXT($A2,"0"),Orders_Descriptions)*SEARCH(" Parts "," "&Orders_ChargeTypes&" ")),Orders_Amounts)
(note with the above the ISNUMBER can be applied to the product of the SEARCHes, need not be applied to each SEARCH separately)

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Originally Posted by Merf

...although the documented syntax for SUMPRODUCT shows a COMMA between the various terms, apparently you must enclose logical terms in parentheses and use an ASTERISK (rather than a comma) between them.

Not quite sure WHY this is so, but it apparently works!

Hi, refer to the point re: coercion in my prior post -- so you can use either:

=SUMPRODUCT((boolean array)+0,numeric_range)
or
=SUMPRODUCT((boolean)*numeric_range)

both do the same thing in that they are coercing the logicals (boolean array) to their native XL integer equivalents of 1 (TRUE) and 0 (FALSE)

If the arrays in your SUMPRODUCT share the same dimensions it is generally a good idea to split the logicals from the "numeric" range, so using the first rather than second approach

the reason for this is to ape the behaviour of SUMIF, so assume your "numeric" range also stored text values (incl. null strings from formulas), this:

=SUMPRODUCT((boolean)*numeric_range) --> #VALUE!

because when you explicitly coerce the numeric_range string it will generate an error, whereas:

=SUMPRODUCT((boolean)+0,numeric_range) --> #

will behave like SUMIF, you coerce the Booleans discretely, and the products themselves will ignore the non-numeric values

the above is just a basic overview, there are good reasons to use * route (e.g. numbers stored as text, or arrays have different dimensions) but, hopefully, this sheds some light.

edit: and so, in the case of two boolean arrays - rather than coerce separately you can multiple these together, and keep separate from the "numeric_range", e.g.

=SUMPRODUCT((boolean)*(boolean),numeric_range)

key is still that we're coercing the booleans discretely relative to the "numeric range" - for sake of robustness