| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct but sometimes don't test some criteria
On of my criteria is ....*(StoreArray=Store)*......
But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
What about if you use ....*OR(StoreArray=Store,Store="All")*......
Rick "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
Try something like this:
=SUMPRODUCT(ISNUMBER(MATCH(IF(Store="All","*",Stor eArray),Store,0))*Amounts) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
One other thought....if your calculations needs are simple...
maybe this: =SUMIF(StoreArray,IF(E1="All","*",Store),Amounts) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Ron Coderre" wrote in message ... Try something like this: =SUMPRODUCT(ISNUMBER(MATCH(IF(Store="All","*",Stor eArray),Store,0))*Amounts) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
Hi Rick, this ruturns all stores when Store = "All" and when Store = "Atlanta
Store" (of course I don't want all stores when Store = a specific store). I love how simple it is though, any small tweek to get it to work? "Rick Rothstein (MVP - VB)" wrote: What about if you use ....*OR(StoreArray=Store,Store="All")*...... Rick "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
Very clever Ron. Worked great. But sometimes I use sumproduct like this:
........,--(StoreArray=Store),...... But when I used your solution in this style sumproduct and range Store = "All" I get value error. Any idea why? I did: .......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... "Ron Coderre" wrote: Try something like this: =SUMPRODUCT(ISNUMBER(MATCH(IF(Store="All","*",Stor eArray),Store,0))*Amounts) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
No, too many criteria, I'm just showing the one I'm having a problem with.
"Ron Coderre" wrote: One other thought....if your calculations needs are simple... maybe this: =SUMIF(StoreArray,IF(E1="All","*",Store),Amounts) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Ron Coderre" wrote in message ... Try something like this: =SUMPRODUCT(ISNUMBER(MATCH(IF(Store="All","*",Stor eArray),Store,0))*Amounts) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
It may be time to post your entire formula and some sample data.
-------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... Very clever Ron. Worked great. But sometimes I use sumproduct like this: .......,--(StoreArray=Store),...... But when I used your solution in this style sumproduct and range Store = "All" I get value error. Any idea why? I did: ......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... "Ron Coderre" wrote: Try something like this: =SUMPRODUCT(ISNUMBER(MATCH(IF(Store="All","*",Stor eArray),Store,0))*Amounts) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
Ooops! I may have spotted the problem....
Try this: .......,--ISNUMBER(MATCH(IF(Store="All","*",StoreArray),Stor e,0)),..... instead of this: .......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... I replaced "All" with an asterisk (*). Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... Very clever Ron. Worked great. But sometimes I use sumproduct like this: .......,--(StoreArray=Store),...... But when I used your solution in this style sumproduct and range Store = "All" I get value error. Any idea why? I did: ......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... "Ron Coderre" wrote: Try something like this: =SUMPRODUCT(ISNUMBER(MATCH(IF(Store="All","*",Stor eArray),Store,0))*Amounts) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
If that doesn't work, then I am not understanding something about your
original set up as posted. Unless I am just tired, this .....*OR(StoreArray=Store,Store="All")*...... should produce the same results as this when Store does not equal "All"... .....*(StoreArray=Store)*...... You said the latter worked when Store is not equal to "All". If Store does equal "All", then my OR statement will evaluate to TRUE which, when multiplied by your other terms, is converted to a 1 (which is what your IF statement proposal suggested you wanted to happen). So, what am I missing about your setup that my OR statement isn't working for you? Rick "Aaron" wrote in message ... Hi Rick, this ruturns all stores when Store = "All" and when Store = "Atlanta Store" (of course I don't want all stores when Store = a specific store). I love how simple it is though, any small tweek to get it to work? "Rick Rothstein (MVP - VB)" wrote: What about if you use ....*OR(StoreArray=Store,Store="All")*...... Rick "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
Rick
The OR function returns a single value, not an array, So when any member of StoreArray matches Store, the function returns TRUE and SUMPRODUCT will include ALL stores. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Rick Rothstein (MVP - VB)" wrote in message ... If that doesn't work, then I am not understanding something about your original set up as posted. Unless I am just tired, this ....*OR(StoreArray=Store,Store="All")*...... should produce the same results as this when Store does not equal "All"... ....*(StoreArray=Store)*...... You said the latter worked when Store is not equal to "All". If Store does equal "All", then my OR statement will evaluate to TRUE which, when multiplied by your other terms, is converted to a 1 (which is what your IF statement proposal suggested you wanted to happen). So, what am I missing about your setup that my OR statement isn't working for you? Rick "Aaron" wrote in message ... Hi Rick, this ruturns all stores when Store = "All" and when Store = "Atlanta Store" (of course I don't want all stores when Store = a specific store). I love how simple it is though, any small tweek to get it to work? "Rick Rothstein (MVP - VB)" wrote: What about if you use ....*OR(StoreArray=Store,Store="All")*...... Rick "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
No, you had given me "*" the first time and I changed it to "All"(it just
made more sense to me this way). They both work in the ...*()*... style but neither work in the .....,--(),.... style. Per your request, here is my formula: =SUMPRODUCT(--(SDDte<=$A13),--(SDDte=EOMONTH($A13,-12)+1),--ISNUMBER(MATCH(IF(Store="All","*",SDStr),Store,0)) ,(ASOC)) Basically SDDte is a range of dates, A13 is a particular date, Store is a particular store, SDStr is a range of stores, and ASOC is adjacent to my range of dates and stores and I am summing it. There are no bugs in the formula except when I replaced .....,--(SDStr=Store),.... with .....,--ISNUMBER(MATCH(IF(Store="All","*",SDStr),Store,0)) ,..... I get value error when Store = "All" But your solution works perfectly in .....*()*.... style sumproducts. "Ron Coderre" wrote: Ooops! I may have spotted the problem.... Try this: .......,--ISNUMBER(MATCH(IF(Store="All","*",StoreArray),Stor e,0)),..... instead of this: .......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... I replaced "All" with an asterisk (*). Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... Very clever Ron. Worked great. But sometimes I use sumproduct like this: .......,--(StoreArray=Store),...... But when I used your solution in this style sumproduct and range Store = "All" I get value error. Any idea why? I did: ......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... "Ron Coderre" wrote: Try something like this: =SUMPRODUCT(ISNUMBER(MATCH(IF(Store="All","*",Stor eArray),Store,0))*Amounts) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
Posting the formula helped quite a bit.....
See if this works for you (in sections, for readability): =SUMPRODUCT(--(SDDTE<=$B13),--(SDDTE=EOMONTH($B13,-12)+1),--( SDStr=IF(Store="All",SDStr,Store)),(ASOC)) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... No, you had given me "*" the first time and I changed it to "All"(it just made more sense to me this way). They both work in the ...*()*... style but neither work in the ....,--(),.... style. Per your request, here is my formula: =SUMPRODUCT(--(SDDte<=$A13),--(SDDte=EOMONTH($A13,-12)+1),--ISNUMBER(MATCH(IF(Store="All","*",SDStr),Store,0)) ,(ASOC)) Basically SDDte is a range of dates, A13 is a particular date, Store is a particular store, SDStr is a range of stores, and ASOC is adjacent to my range of dates and stores and I am summing it. There are no bugs in the formula except when I replaced ....,--(SDStr=Store),.... with .....,--ISNUMBER(MATCH(IF(Store="All","*",SDStr),Store,0)) ,..... I get value error when Store = "All" But your solution works perfectly in ....*()*.... style sumproducts. "Ron Coderre" wrote: Ooops! I may have spotted the problem.... Try this: .......,--ISNUMBER(MATCH(IF(Store="All","*",StoreArray),Stor e,0)),..... instead of this: .......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... I replaced "All" with an asterisk (*). Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... Very clever Ron. Worked great. But sometimes I use sumproduct like this: .......,--(StoreArray=Store),...... But when I used your solution in this style sumproduct and range Store = "All" I get value error. Any idea why? I did: ......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... "Ron Coderre" wrote: Try something like this: =SUMPRODUCT(ISNUMBER(MATCH(IF(Store="All","*",Stor eArray),Store,0))*Amounts) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
In my original failed attempt the 1 (no array) worked perfectly. The problem
was when Store < "ALL" I need the array result of StoreArray=Store, which my if statment did not give me and your or statement did not give me. "Ron Coderre" wrote: Rick The OR function returns a single value, not an array, So when any member of StoreArray matches Store, the function returns TRUE and SUMPRODUCT will include ALL stores. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Rick Rothstein (MVP - VB)" wrote in message ... If that doesn't work, then I am not understanding something about your original set up as posted. Unless I am just tired, this ....*OR(StoreArray=Store,Store="All")*...... should produce the same results as this when Store does not equal "All"... ....*(StoreArray=Store)*...... You said the latter worked when Store is not equal to "All". If Store does equal "All", then my OR statement will evaluate to TRUE which, when multiplied by your other terms, is converted to a 1 (which is what your IF statement proposal suggested you wanted to happen). So, what am I missing about your setup that my OR statement isn't working for you? Rick "Aaron" wrote in message ... Hi Rick, this ruturns all stores when Store = "All" and when Store = "Atlanta Store" (of course I don't want all stores when Store = a specific store). I love how simple it is though, any small tweek to get it to work? "Rick Rothstein (MVP - VB)" wrote: What about if you use ....*OR(StoreArray=Store,Store="All")*...... Rick "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
Exactly what I was trying to do! Perfect solution! Sorry, I try to respect
your time by posting short questions without "meaningless" details. Clearly I left some important details out and that backfired.... Thanks so much "Ron Coderre" wrote: Posting the formula helped quite a bit..... See if this works for you (in sections, for readability): =SUMPRODUCT(--(SDDTE<=$B13),--(SDDTE=EOMONTH($B13,-12)+1),--( SDStr=IF(Store="All",SDStr,Store)),(ASOC)) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... No, you had given me "*" the first time and I changed it to "All"(it just made more sense to me this way). They both work in the ...*()*... style but neither work in the ....,--(),.... style. Per your request, here is my formula: =SUMPRODUCT(--(SDDte<=$A13),--(SDDte=EOMONTH($A13,-12)+1),--ISNUMBER(MATCH(IF(Store="All","*",SDStr),Store,0)) ,(ASOC)) Basically SDDte is a range of dates, A13 is a particular date, Store is a particular store, SDStr is a range of stores, and ASOC is adjacent to my range of dates and stores and I am summing it. There are no bugs in the formula except when I replaced ....,--(SDStr=Store),.... with .....,--ISNUMBER(MATCH(IF(Store="All","*",SDStr),Store,0)) ,..... I get value error when Store = "All" But your solution works perfectly in ....*()*.... style sumproducts. "Ron Coderre" wrote: Ooops! I may have spotted the problem.... Try this: .......,--ISNUMBER(MATCH(IF(Store="All","*",StoreArray),Stor e,0)),..... instead of this: .......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... I replaced "All" with an asterisk (*). Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... Very clever Ron. Worked great. But sometimes I use sumproduct like this: .......,--(StoreArray=Store),...... But when I used your solution in this style sumproduct and range Store = "All" I get value error. Any idea why? I did: ......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... "Ron Coderre" wrote: Try something like this: =SUMPRODUCT(ISNUMBER(MATCH(IF(Store="All","*",Stor eArray),Store,0))*Amounts) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
I'm glad I could help, Aaron......Thanks for the feedback.
------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... Exactly what I was trying to do! Perfect solution! Sorry, I try to respect your time by posting short questions without "meaningless" details. Clearly I left some important details out and that backfired.... Thanks so much "Ron Coderre" wrote: Posting the formula helped quite a bit..... See if this works for you (in sections, for readability): =SUMPRODUCT(--(SDDTE<=$B13),--(SDDTE=EOMONTH($B13,-12)+1),--( SDStr=IF(Store="All",SDStr,Store)),(ASOC)) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... No, you had given me "*" the first time and I changed it to "All"(it just made more sense to me this way). They both work in the ...*()*... style but neither work in the ....,--(),.... style. Per your request, here is my formula: =SUMPRODUCT(--(SDDte<=$A13),--(SDDte=EOMONTH($A13,-12)+1),--ISNUMBER(MATCH(IF(Store="All","*",SDStr),Store,0)) ,(ASOC)) Basically SDDte is a range of dates, A13 is a particular date, Store is a particular store, SDStr is a range of stores, and ASOC is adjacent to my range of dates and stores and I am summing it. There are no bugs in the formula except when I replaced ....,--(SDStr=Store),.... with .....,--ISNUMBER(MATCH(IF(Store="All","*",SDStr),Store,0)) ,..... I get value error when Store = "All" But your solution works perfectly in ....*()*.... style sumproducts. "Ron Coderre" wrote: Ooops! I may have spotted the problem.... Try this: .......,--ISNUMBER(MATCH(IF(Store="All","*",StoreArray),Stor e,0)),..... instead of this: .......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... I replaced "All" with an asterisk (*). Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... Very clever Ron. Worked great. But sometimes I use sumproduct like this: .......,--(StoreArray=Store),...... But when I used your solution in this style sumproduct and range Store = "All" I get value error. Any idea why? I did: ......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... "Ron Coderre" wrote: Try something like this: =SUMPRODUCT(ISNUMBER(MATCH(IF(Store="All","*",Stor eArray),Store,0))*Amounts) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
Let's see if we understand this...
Store is a *single cell* that may or may not contain "All" ? When Store = All then you want to sum based on the *entire* range StoreArray? When Store < All you want to sum based on StoreArray = SDStr? Use something like this but it has to be array entered** : =SUMPRODUCT(--(IF(Store="all",StoreArray<"",ISNUMBER(MATCH(Stor eArray,SDStr,0)))),ASOC) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Aaron" wrote in message ... No, you had given me "*" the first time and I changed it to "All"(it just made more sense to me this way). They both work in the ...*()*... style but neither work in the ....,--(),.... style. Per your request, here is my formula: =SUMPRODUCT(--(SDDte<=$A13),--(SDDte=EOMONTH($A13,-12)+1),--ISNUMBER(MATCH(IF(Store="All","*",SDStr),Store,0)) ,(ASOC)) Basically SDDte is a range of dates, A13 is a particular date, Store is a particular store, SDStr is a range of stores, and ASOC is adjacent to my range of dates and stores and I am summing it. There are no bugs in the formula except when I replaced ....,--(SDStr=Store),.... with .....,--ISNUMBER(MATCH(IF(Store="All","*",SDStr),Store,0)) ,..... I get value error when Store = "All" But your solution works perfectly in ....*()*.... style sumproducts. "Ron Coderre" wrote: Ooops! I may have spotted the problem.... Try this: .......,--ISNUMBER(MATCH(IF(Store="All","*",StoreArray),Stor e,0)),..... instead of this: .......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... I replaced "All" with an asterisk (*). Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... Very clever Ron. Worked great. But sometimes I use sumproduct like this: .......,--(StoreArray=Store),...... But when I used your solution in this style sumproduct and range Store = "All" I get value error. Any idea why? I did: ......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... "Ron Coderre" wrote: Try something like this: =SUMPRODUCT(ISNUMBER(MATCH(IF(Store="All","*",Stor eArray),Store,0))*Amounts) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
Before anyone points out that this could be done with a simple SUMIF, I
*intentionally* left out all the date comparison stuff! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Let's see if we understand this... Store is a *single cell* that may or may not contain "All" ? When Store = All then you want to sum based on the *entire* range StoreArray? When Store < All you want to sum based on StoreArray = SDStr? Use something like this but it has to be array entered** : =SUMPRODUCT(--(IF(Store="all",StoreArray<"",ISNUMBER(MATCH(Stor eArray,SDStr,0)))),ASOC) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Aaron" wrote in message ... No, you had given me "*" the first time and I changed it to "All"(it just made more sense to me this way). They both work in the ...*()*... style but neither work in the ....,--(),.... style. Per your request, here is my formula: =SUMPRODUCT(--(SDDte<=$A13),--(SDDte=EOMONTH($A13,-12)+1),--ISNUMBER(MATCH(IF(Store="All","*",SDStr),Store,0)) ,(ASOC)) Basically SDDte is a range of dates, A13 is a particular date, Store is a particular store, SDStr is a range of stores, and ASOC is adjacent to my range of dates and stores and I am summing it. There are no bugs in the formula except when I replaced ....,--(SDStr=Store),.... with .....,--ISNUMBER(MATCH(IF(Store="All","*",SDStr),Store,0)) ,..... I get value error when Store = "All" But your solution works perfectly in ....*()*.... style sumproducts. "Ron Coderre" wrote: Ooops! I may have spotted the problem.... Try this: .......,--ISNUMBER(MATCH(IF(Store="All","*",StoreArray),Stor e,0)),..... instead of this: .......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... I replaced "All" with an asterisk (*). Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... Very clever Ron. Worked great. But sometimes I use sumproduct like this: .......,--(StoreArray=Store),...... But when I used your solution in this style sumproduct and range Store = "All" I get value error. Any idea why? I did: ......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... "Ron Coderre" wrote: Try something like this: =SUMPRODUCT(ISNUMBER(MATCH(IF(Store="All","*",Stor eArray),Store,0))*Amounts) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
StoreArray and SDStr are the same thing, I just renamed it for the sake of
communicating onthe bullitin board. When Store < All I want to sum based on StoreArray(SDStr) = Store Ron Gave a very consise perfect solution a moment ago. Thanks for your help and sorry for such a confusing post. "T. Valko" wrote: Let's see if we understand this... Store is a *single cell* that may or may not contain "All" ? When Store = All then you want to sum based on the *entire* range StoreArray? When Store < All you want to sum based on StoreArray = SDStr? Use something like this but it has to be array entered** : =SUMPRODUCT(--(IF(Store="all",StoreArray<"",ISNUMBER(MATCH(Stor eArray,SDStr,0)))),ASOC) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Aaron" wrote in message ... No, you had given me "*" the first time and I changed it to "All"(it just made more sense to me this way). They both work in the ...*()*... style but neither work in the ....,--(),.... style. Per your request, here is my formula: =SUMPRODUCT(--(SDDte<=$A13),--(SDDte=EOMONTH($A13,-12)+1),--ISNUMBER(MATCH(IF(Store="All","*",SDStr),Store,0)) ,(ASOC)) Basically SDDte is a range of dates, A13 is a particular date, Store is a particular store, SDStr is a range of stores, and ASOC is adjacent to my range of dates and stores and I am summing it. There are no bugs in the formula except when I replaced ....,--(SDStr=Store),.... with .....,--ISNUMBER(MATCH(IF(Store="All","*",SDStr),Store,0)) ,..... I get value error when Store = "All" But your solution works perfectly in ....*()*.... style sumproducts. "Ron Coderre" wrote: Ooops! I may have spotted the problem.... Try this: .......,--ISNUMBER(MATCH(IF(Store="All","*",StoreArray),Stor e,0)),..... instead of this: .......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... I replaced "All" with an asterisk (*). Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... Very clever Ron. Worked great. But sometimes I use sumproduct like this: .......,--(StoreArray=Store),...... But when I used your solution in this style sumproduct and range Store = "All" I get value error. Any idea why? I did: ......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... "Ron Coderre" wrote: Try something like this: =SUMPRODUCT(ISNUMBER(MATCH(IF(Store="All","*",Stor eArray),Store,0))*Amounts) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
Ron Gave a very consise perfect solution a moment ago.
He has a habit of doing that! Glad you got it sorted out. -- Biff Microsoft Excel MVP "Aaron" wrote in message ... StoreArray and SDStr are the same thing, I just renamed it for the sake of communicating onthe bullitin board. When Store < All I want to sum based on StoreArray(SDStr) = Store Ron Gave a very consise perfect solution a moment ago. Thanks for your help and sorry for such a confusing post. "T. Valko" wrote: Let's see if we understand this... Store is a *single cell* that may or may not contain "All" ? When Store = All then you want to sum based on the *entire* range StoreArray? When Store < All you want to sum based on StoreArray = SDStr? Use something like this but it has to be array entered** : =SUMPRODUCT(--(IF(Store="all",StoreArray<"",ISNUMBER(MATCH(Stor eArray,SDStr,0)))),ASOC) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Aaron" wrote in message ... No, you had given me "*" the first time and I changed it to "All"(it just made more sense to me this way). They both work in the ...*()*... style but neither work in the ....,--(),.... style. Per your request, here is my formula: =SUMPRODUCT(--(SDDte<=$A13),--(SDDte=EOMONTH($A13,-12)+1),--ISNUMBER(MATCH(IF(Store="All","*",SDStr),Store,0)) ,(ASOC)) Basically SDDte is a range of dates, A13 is a particular date, Store is a particular store, SDStr is a range of stores, and ASOC is adjacent to my range of dates and stores and I am summing it. There are no bugs in the formula except when I replaced ....,--(SDStr=Store),.... with .....,--ISNUMBER(MATCH(IF(Store="All","*",SDStr),Store,0)) ,..... I get value error when Store = "All" But your solution works perfectly in ....*()*.... style sumproducts. "Ron Coderre" wrote: Ooops! I may have spotted the problem.... Try this: .......,--ISNUMBER(MATCH(IF(Store="All","*",StoreArray),Stor e,0)),..... instead of this: .......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... I replaced "All" with an asterisk (*). Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... Very clever Ron. Worked great. But sometimes I use sumproduct like this: .......,--(StoreArray=Store),...... But when I used your solution in this style sumproduct and range Store = "All" I get value error. Any idea why? I did: ......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),St ore,0)),..... "Ron Coderre" wrote: Try something like this: =SUMPRODUCT(ISNUMBER(MATCH(IF(Store="All","*",Stor eArray),Store,0))*Amounts) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
|
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumProduct but sometimes don't test some criteria
SUMPRODUCT... StoreArray.... Store***Array***....
I missed the array part of StoreArray and its relation to the SUMPRODUCT function.. I told you I was tired<g Thanks for pointing that out to me. Rick "Ron Coderre" wrote in message ... Rick The OR function returns a single value, not an array, So when any member of StoreArray matches Store, the function returns TRUE and SUMPRODUCT will include ALL stores. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Rick Rothstein (MVP - VB)" wrote in message ... If that doesn't work, then I am not understanding something about your original set up as posted. Unless I am just tired, this ....*OR(StoreArray=Store,Store="All")*...... should produce the same results as this when Store does not equal "All"... ....*(StoreArray=Store)*...... You said the latter worked when Store is not equal to "All". If Store does equal "All", then my OR statement will evaluate to TRUE which, when multiplied by your other terms, is converted to a 1 (which is what your IF statement proposal suggested you wanted to happen). So, what am I missing about your setup that my OR statement isn't working for you? Rick "Aaron" wrote in message ... Hi Rick, this ruturns all stores when Store = "All" and when Store = "Atlanta Store" (of course I don't want all stores when Store = a specific store). I love how simple it is though, any small tweek to get it to work? "Rick Rothstein (MVP - VB)" wrote: What about if you use ....*OR(StoreArray=Store,Store="All")*...... Rick "Aaron" wrote in message ... On of my criteria is ....*(StoreArray=Store)*...... But somtimes the range Store = "All" which is not a value in StoreArray. So I tried .....*(If(store="all",1,StoreArray=Store))*..... The 1 has the desired effect of skipping this criteria. But my false statement is not having the desired effect. Any idea how I can do this? (I'm trying not to do a big if statement in the begining with one sumproduct scenerio if true and anther if false) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT using more than 2 criteria? | Excel Worksheet Functions | |||
| Sumproduct - two+ criteria | Excel Worksheet Functions | |||
| Sumproduct 4 criteria | Excel Worksheet Functions | |||
| Test Cell For Multiple Criteria | Excel Worksheet Functions | |||
| Using Error Message as test Criteria | Excel Worksheet Functions | |||
Linear Mode
