SumProduct but sometimes don't test some criteria

[Aaron]

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)

 

[Rick Rothstein \(MVP - VB\)]

What about if you use ....*OR(StoreArray=Store,Store="All")*......

Rick

 

[Ron Coderre]

Try something like this:

=SUMPRODUCT(ISNUMBER(MATCH(IF(Store="All","*",StoreArray),Store,0))*Amounts)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Coderre]

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)

 

[Aaron]

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?

 

[Aaron]

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),Store,0)),.....

 

[Aaron]

No, too many criteria, I'm just showing the one I'm having a problem with.

 

[Ron Coderre]

It may be time to post your entire formula and some sample data.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Coderre]

Ooops! I may have spotted the problem....

Try this:
.......,--ISNUMBER(MATCH(IF(Store="All","*",StoreArray),Store,0)),.....

instead of this:
.......,--ISNUMBER(MATCH(IF(Store="All","All",StoreArray),Store,0)),.....

I replaced "All" with an asterisk (*).

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Coderre]

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)

 

[Aaron]

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]

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]

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.

 

[Aaron]

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]

I'm glad I could help, Aaron......Thanks for the feedback.

-------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[T. Valko]

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(StoreArray,SDStr,0)))),ASOC)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

Before anyone points out that this could be done with a simple SUMIF, I
*intentionally* left out all the date comparison stuff!

 

[Aaron]

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]

Ron Gave a very consise perfect solution a moment ago.

He has a habit of doing that!

Glad you got it sorted out.