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") ?


"John Michl" wrote:

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