Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct wildcard or "all" condition
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Sumproduct with condition??? | Excel Discussion (Misc queries) | |||
wildcard in sumproduct? | Excel Worksheet Functions | |||
If condition is true return sumproduct of two arrays | Excel Worksheet Functions | |||
Sumproduct with Wildcard * | Excel Worksheet Functions |