Formula Help - SumProduct with multiple Conditions that include a wildcard

[Johnny Thunder]

Hello All,

I am hoping this is an easy one, I have a sumproduct formula that looks at multiple conditions and it works great but the business just threw in a new variable and I was hoping it will be a quick modification to the formula to get it to work.

Here is the formula:

=IF($C37="",IFERROR(SUMPRODUCT((OPEX_OCOGS=$B37) * (GLGRoup=$D37) * (Hdata=J$2 & " N") * (Hdata=J$2 & " Y") *DataTable)/1,0), IFERROR(SUMPRODUCT((OPEX_OCOGS=$B37)*(GLGRoup=$D37)*(Hdata=J$2 & " " & $C37)*DataTable)/1,0))

So, the new variable is the IF(C37="") then for the Hdata part of the formula include both J$2 and a Letter. The Hdata lookup part of the formula normally accounts for just one letter at a time, N or Y but if the user leaves the field blank it means the formula needs to bring in both Y and N fields.

Just an FYI - J$2 & " N" = "Q1 N" or "Q1 Y" for all 4 quarters.

So I manually duplicated the HData part of the Formula twice within as you can see above and the formula fails because it seems like it can't handle the same variable twice with two different conditions. I think an easy solution would be to just add a "+" symbol and repeat the formula, 1 for the "N" and another for the "Y" part of the variable.

I was hoping there was an easier way to do this like "Q1 *" and that way the wildcard could be for "Y or N"?

Any Ideas?

 

[Eric W]

Try:

=SUMPRODUCT((OPEX_OCOGS=$B37)*(GLGRoup=$D37)*((Hdata=J$2&" "&$C37)+($C37="")*(LEFT(Hdata,LEN(J$2))=J$2))*DataTable)