Sumproduct wildcard or "all" condition

[John Michl]

Let's say I have a database of fruit sales by store.

There are named data ranges called: Fruit_data,Store_data, Sales_data
and Month_data and all data is stored in a Sheet called "Data".

In the sheet called "Report",
A1= "Store" B1 = drop down validation list including "All", "East
Store", "West Store"
A2 = "Fruit" B2 = drop down validation list including "All",
"Apple", "Banana", "Cherry"
A3 = "Month" B3 = drop down list including "All" and each unique
month in the data table

The data validation lists include all unique stores or fruits plus the
"All Stores/Fruits". Think of it as a page field in a PivotTable.
(Which I can't use in this case for certain reasons.)

Obviously, "All" is not a unique store or fruit name but is meant to
indicate "All" stores or fruits. "All" is not in the data table.

I'd like to build a single SUMPRODUCT formula that would know to
disregard the Store or Fruit criteria should "All" be selected for the
appropriate list in B1 or B2.

Here's the formula now....

=SUMPRODUCT(--(Store_data=B1),--(Fruit_data=B2),--(Month_data=B3),(Sales_data))

I'd like the formula to disregard a criteria if the criteria cell is
equal to "All". Of course, I could do a big set of nested IF
statements but my actual formula is much more complex than this simple
example so I'm hoping for some creative solutions. Note that my data
validation will always match the unique set of stores, fruits or months
except that the list has "All" tacked to the beginning.

Thanks for the help.

- John
http://johnmichl.com/exceltips.htm

 

[Guest]

Sumproduct will evaluate each statement and assign a value of 1 if true and 0
if false. Then it multiplies all the statements. If you have a false in any
of the statements, you will get a "0". Have you tried to add a statement to
the sumproduct that tests for the "all" such as

--(Fruit_data<>"all") ?

 

[Biff]

I'd like the formula to disregard a criteria if the criteria cell is

equal to "All".

Don't you mean that you want to include ALL of that particular criteria? For
example, if you choose ALL stores then calculate for ALL stores.

I could do a big set of nested IF statements

What do you consider a big set of nested IF's? You would need one IF for
each array (except the last array: Sales_Data).

What format is your Month_Data? Is it a month name like June? Is it the
month number like 6? Is it a date like 6/1/2006?

Biff

 

[John Michl]

Ik and Biff - thanks for the help.

I could use Ik's approach to get all of the fruits but as far as I
know, I'd need to use --(Fruit_data<>"All") if I wanted everything and
--(Fruit_data=B2) for a specific fruit. This means I'd need to use a
nested if to determine which one to use. In my simple example, this
wouldn't be a big deal but in reality my formula is much more complex.
I'm using sumproduct to calculate standard deviation and several other
stats so I'd like to avoid nested IFs if possible. In order to
calculate standard deviation, several of the criteria are repeated
several times in the formula so the more criteria, the more IF
statements.

Is there a wildcard character that can be used in SUMPRODUCT. If so, I
could put an IF statement in another cell (say C2 for the fruit
criteria.) It could be something like, =IF(B2="All,"*",B2) where * is
the wildcard character. That way I can still use the equals sign in
the --(Fruit_data=C2) portion.

Any ideas?

- John

 

[Biff]

Is there a wildcard character that can be used in SUMPRODUCT.

No. Sumproduct will not directly accept wildcards. You can use wildcards in
other functions nested within Sumproduct.

Biff