SumProduct function with Wildcard or Alternative

[TheBlade]

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

The forumlae are in a table, the structure of which needs to remain static (as opposed to pivottables) for charts. I am trying 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

Basic/cutdown example of my problem:

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

Sometimes the variable references (e.g. L1 for Hub) return blank in cases where users want to see the combined result for ALL hubs. This is functionality I want to accomodate.

If anyone has any ideas would be appreciated.

Thanks!

 

[SkipVought]

Why limit your options???

"the structure of which needs to remain static (as opposed to pivottables) for charts"

How can I rename a table as it changes size faq68-1331

It would sure help if you posted some sample data that demonstrates, your caveat.




Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[TheBlade]

Eureka! Figured it out. (SkipVought - the model currently uses sumproduct, just adding to its functionality, and not enough time to overhaul the structure unfortunately).

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.