SumProduct with Wildcard

[GeekGirlau]

I've had a look at the posts re SumProduct with a wildcard, but my problem is a little different.

I have some criteria that may be blank, in which case I don't want them included in the SumProduct at all.

My actual SumProduct formula requires an additional 3-4 criteria, but I've shortened it in the examples below. The ranges critSupplier and critCategory may all be filled, may all be blank, or either of them may be blank.

Neither of the following work:

=SUMPRODUCT(IF(critSupplier<>"", (Vendor=critSupplier),1) * IF(critCategory<>"", (SalesCat=critCategory),1) * Sales2004)

=SUMPRODUCT((Vendor=IF(critSupplier="", "*", critSupplier)) * (SalesCat=IF(critCategory="", "*", critCategory)) * Sales2004)

I know I can have a nasty formula such as the following:

=IF(AND(critSupplier="",critCategory=""), SUMPRODUCT(Sales2004), IF(critSupplier="",SUMPRODUCT((SalesCat=critCategory) * Sales2004), IF(critCategory="",SUMPRODUCT((Vendor=critSupplier) * (SalesCat=critCategory) * Sales2004),SUMPRODUCT((Vendor=critSupplier) * (SalesCat=critCategory) * Sales2004))))

but I was hoping for a more elegant (and shorter!) solution - any ideas?

 

[GeekGirlau]

Oops - the nasty formula should read:

=IF(AND(critSupplier="",critCategory=""), SUMPRODUCT(Sales2004), IF(critSupplier="",SUMPRODUCT((SalesCat=critCategory) * Sales2004), [Red]IF(critCategory="",SUMPRODUCT((Vendor=critSupplier) * Sales2004)[/Red],SUMPRODUCT((Vendor=critSupplier) * (SalesCat=critCategory) * Sales2004))))

 

[Zathras]

Please consider using DSUM if only for the sake of whoever comes after you and has to maintain the spreadsheet.

 

[TheBlade]

Hi, I have this exact problem too, and prefer sumproduct as the table structure needs to remain static (as opposed to pivottables) for charts. Anyone have any ideas or solution?
Thanks

 

[GeekGirlau]

I ended up creating a calculation workbook (actually 3 of them due to file size) using SUMPRODUCT, then DSUM on the report to consolidate the results and allow the required flexibility. The workbooks are set to manual calculation so changing the criteria on the report calculates the active sheet only, and the DSUM runs fairly quickly. Once a week the calculation workbooks are updated overnight, as calculation takes a few hours to run.

I don't know that it's any easier to maintain as there are now tonnes of hidden criteria ranges, and I still need the SUMPRODUCT in the background, but it works in a fashion!

 

[TheBlade]

Thanks, I tried to stay away from Dsum, as each row in my tables has different criteria (based on user input form, row and col headings etc.), which with Dsum would require lots of hidden criteria ranges, sounds similar to yours. Tried searching everywhere for a Sumproduct solution but to no avail. I guess I'll have to try something different.
cheers

 

[SkipVought]

GG,

Another possibility would be to use MS Query, using the ActiveWorkbook as the DATABASE.

You can make the query a PARAMETER query (by using [What value?] in the Criteria which forces the user to enter a criteria value. It can be further "automated" by the THIRD option in the Parameters window where parameters can be picked off the worksheet.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[TheBlade]

Basic/cutdown example of my problem:

=SUMPRODUCT(Mth*(Product=$A38)*(Hub=L1)*(Measure=L2)*(Type=L3))

Sometimes L1 (Hub variable) will return blank in cases where users want to see the combined result for ALL hubs...same for Product, Type etc.

If anyone has any ideas would be appreciated.

Thanks!

 

[SkipVought]

TB,

Please post your question in a NEW THREAD.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[TheBlade]

Eureka! Figured it out.

Using the example:

=SUMPRODUCT(Mth*(Product=$A38)*(Hub=L1)*(Measure=L2)*(Type=L3))

To accomodate ALL Hubs when cell L1 is blank

=SUMPRODUCT(Mth*(Product=$A38)*(Hub=if(L1="",Hub,L1))*(Measure=L2)*(Type=L3))


and so on and so forth for any other conditions that may require returning all.