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