Sum Product Issue

[mapsamountainrange]

So I'm using a SumProduct array to sum values from an input sheet if they match both a row label (month) and a column label. This has been working well, but now I'm trying to expand it to sum if it matches either of two column labels. Here's the original formula:

=SUMPRODUCT(NOT(ISERROR(SEARCH($A10,'Input Sheet'!$A$18:$A$29)))
*NOT(ISERROR(SEARCH("Billed Peak kW",'Input Sheet'!$B$17:$U$17)))
*IF(ISNUMBER('Input Sheet'!$B$18:$U$29),'Input Sheet'!$B$18:$U$29,0))

The first row of that matches the row labels, the second row matches the column header, and the third row returns the actual values to be summed.

But if I expand the formula to:

=SUMPRODUCT(NOT(ISERROR(SEARCH($A10,'Input Sheet'!$A$18:$A$29)))
*OR(NOT(ISERROR(SEARCH("Billed Peak kW",'Input Sheet'!$B$17:$U$17))),NOT(ISERROR(SEARCH("Billed kW",'Input Sheet'!$B$17:$U$17))))
*IF(ISNUMBER('Input Sheet'!$B$18:$U$29),'Input Sheet'!$B$18:$U$29,0))

It returns all values that match the month row, even columns that don't match either of those criteria. Each will work individually, but not in that OR(). I would just add the two together, but the formula needs to be able to be updated by a macro, and the addition version is too long and gives me a "1004, Unable to set the FormulaArray property of Range Class" error.

Any thoughts?

 

[mapsamountainrange]

In case anyone was interested, I resolved this issue by adding the two NOT(ISERROR(SEARCH())) terms rather than putting them in an OR(). The two terms alternate between 0 and 1 and are mutually exclusive so it functionally is the same. I still don't have a good explanation why the OR() doesn't work, and the formula was still over the 255 VBA character limit (resolved in other ways), but I thought I'd share what I learned.

=SUMPRODUCT(NOT(ISERROR(SEARCH($A10,'Input Sheet'!$A$18:$A$29)))
*(NOT(ISERROR(SEARCH("Billed Peak kW",'Input Sheet'!$B$17:$U$17))) + NOT(ISERROR(SEARCH("Billed kW",'Input Sheet'!$B$17:$U$17)))))
*IF(ISNUMBER('Input Sheet'!$B$18:$U$29),'Input Sheet'!$B$18:$U$29,0))

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(SEARCH($A10,'Input Sheet'!$A$18:$A$29)),IF(ISNUMBER(SEARCH("Billed*kW",'Input Sheet'!$B$17:$U$17)),IF(ISNUMBER('Input Sheet'!$B$18:$U$29),'Input Sheet'!$B$18:$U$29))))

Does this work for you?

 

[mapsamountainrange]

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(SEARCH($A10,'Input Sheet'!$A$18:$A$29)),IF(ISNUMBER(SEARCH("Billed*kW",'Input Sheet'!$B$17:$U$17)),IF(ISNUMBER('Input Sheet'!$B$18:$U$29),'Input Sheet'!$B$18:$U$29))))

Does this work for you?

No, that isn't the issue. I've been evaluting the formulas that way the whole time. The original version that was fine wouldn't have worked otherwise.

 

[mapsamountainrange]

No, that isn't the issue. I've been evaluting the formulas that way the whole time. The original version that was fine wouldn't have worked otherwise.

Sorry, just realized I wasn't clear in my reaponse. I can't use the wildcard formatting like that because there are other columns that would be picked up by that (Billed Base kW, etc) and I need only columns matching those two cases.

 

[Aladin Akyurek]

Sorry, just realized I wasn't clear in my reaponse. I can't use the wildcard formatting like that because there are other columns that would be picked up by that (Billed Base kW, etc) and I need only columns matching those two cases.

Right... Control+shift+enter:

=SUM(IF(ISNUMBER(SEARCH($A10,'Input Sheet'!$A$18:$A$29)),IF(ISNUMBER(MATCH('Input Sheet'!$B$17:$U$17,{"Billed kW","Billed Peak kW"},0)),IF(ISNUMBER('Input Sheet'!$B$18:$U$29),'Input Sheet'!$B$18:$U$29))))