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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct wildcard or "all" condition
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct wildcard or "all" condition
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" wrote in message ups.com... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct wildcard or "all" condition
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 wrote: 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" wrote in message ups.com... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct wildcard or "all" condition
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 "John Michl" wrote in message ups.com... 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 wrote: 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" wrote in message ups.com... 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 |
Reply |
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 |