Formula Help - SumProduct with multiple Conditions that include a wildcard

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
677
Office Version
  1. 2016
Platform
  1. MacOS
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
Joined
Aug 18, 2015
Messages
10,825
Try:

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

Watch MrExcel Video

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top