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