SUMPRODUCT - 3 criteria - OR?

**durktj** · 10-01-2008, 12:47 PM

Goodafternoon all,

I have a Source file from which my Sumproduct function pulls its data.
The three criteria columns are W, X, and Y, and the column that is summed is P.

Acceptable criteria for column W includes {3,5,8,10,12,99}. Anything else is considered "Unallocated".
Acceptable criteria for column X includes {1-24,99}. Anything else is considered "Unallocated".
Acceptable criteria for column Y includes {1-17,99}. Anything else is considered "Unallocated".

I utilize SUMPRODUCT to sum column P of the Source tab for all acceptable criteria without issue using variations for each acceptable criteria of this equation:

Please Login or Register  to view this content.

My issue is identifying the cells that are not included in these calculations, ie my "Unallocated".

I think the easiest way to break this down, is to have 3 cells, one for column W unallocated, one for X "unallocated" and one for Y "unallocated", and total.

However, I can get the W unallocated no problem, but when I try for X unallocated, I cant tell excel to exclude those cells already counted in my W unallocated column.

Essentially, in English, what I'm after is:
W Unallocated = SUMPRODUCT where W <> {3,5,8,10,12,99} - I'm all set with this one.

X Unallocated = SUMPRODUCT where w must equal either {3,5,8,10,12,99}, and X <> {1-24,99}.

Y Unallocated = SUMPRODUCT where w equals either {3,5,8,10,12,99}, AND and X = either {1-24,99}, and Y <> {1-17,99}.

this will avoid my doubling counting issue, just can't get the formula down

Any ideas? Thanks!

**rwgrietveld** · 10-02-2008, 09:07 AM

The general idea is this: Find all OR's and subtract all AND's . I see you have found your way with Array functions so you'll figure this one out: here is an example: + is being the OR and * being the AND

Please Login or Register  to view this content.

**Domenic** · 10-03-2008, 09:25 PM

X Unallocated = SUMPRODUCT where w must equal either {3,5,8,10,12,99}, and X <> {1-24,99}

=SUMPRODUCT(--ISNUMBER(MATCH($W$2:$W$5000,{3,5,8,10,12,99},0)),--ISNA(MATCH($X$2:$X$5000,$A$2:$A$26,0)),$P$2:$P$5000)

...where A2:A26 contains 1, 2, 3, ... 24, and 99.

Y Unallocated = SUMPRODUCT where w equals either {3,5,8,10,12,99}, AND and X = either {1-24,99}, and Y <> {1-17,99}

=SUMPRODUCT(--ISNUMBER(MATCH($W$2:$W$5000,{3,5,8,10,12,99},0)),--ISNUMBER(MATCH($X$2:$X$5000,$A$2:$A$26,0)),--ISNA(MATCH($Y$2:$Y$5000,$B$2:$B$19,0)),$P$2:$P$5000)

...where B2:B19 contains 1, 2, 3, ... 17, and 99.

Hope this helps!

Hope this helps!

**durktj** · 10-08-2008, 03:31 PM

Thanks for the resonses! I havent had a chance to dig into them yet. I will let you know!! Thanks again!!!

**psytroniks** · 12-01-2012, 12:37 AM

Originally Posted by rwgrietveld

The general idea is this: Find all OR's and subtract all AND's . I see you have found your way with Array functions so you'll figure this one out: here is an example: + is being the OR and * being the AND

Please Login or Register  to view this content.

I realize this is a really old post, but it was the only post I was able to find that helped me figure out conceptually how to solve my problem. However, my issue is that I have two separate OR conditions that I need to implement and I'm not able to figure out how to subtract all the AND's properly.

My or formula is as follows:

Please Login or Register  to view this content.

Basically what I was trying to do is allow the user to select from two lists (Cells A1 and A2) that each show 3 values (A, B, or Both and C, D, or Both respectively) for their respective variables. The formula should then allow the users to see 1 of 9 possible combinations depending on the combination of the two selected values.

I'm able to get it to work if I just pick one of the variables by subtracting from the formula a copy of itself with all of the +'s replaced with *'s. However this doesn't work with the more complex formula above as the AND combination that replacing all the +'s creates does not actually exist in the data.

I hope that makes sense to someone.

Please Help!

**vlady** · 12-01-2012, 05:25 AM

@ psytroniks welcome to the forum, you can create your own thread and possible put a real workbook and expected outcome.

as per forum rules,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.