# Formula Help - SumProduct with multiple Conditions that include a wildcard

#### Johnny Thunder

##### Well-known Member
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?

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Eric W

##### MrExcel MVP
Try:

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

Replies
1
Views
266
Replies
3
Views
553
Replies
15
Views
1K
Replies
1
Views
153
Replies
1
Views
515

1,130,157
Messages
5,640,460
Members
417,143
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back