Solved How to add a range if multiple criteria

Microsoft Office 2007 home and student
June 2, 2011 at 10:43:59
Specs: Windows Vista
I am trying to create a formula that looks for more than one thing.

If there is an "a" in column X, and EITHER an "a" in column Q OR column R, then I want it to show the amount in column P.

The formula I have tried is
=SUMIF(AND(X8="a",OR(Q8="a",R8="a")),P8)

I am definitely missing something and would appreciate help with this.

Thanks.


See More: How to add a range if multiple criteria

Report •

#1
June 2, 2011 at 19:07:50
✔ Best Answer
Not completely sure, but this formula seems to do what you need:

=SUMPRODUCT(--(X1:X60="a"),((Q1:Q60="a")+(R1:R60="a")),(P1:P60))

The only caveat is you can have only ONE occurrence of the target letters,
Only one row at a time, else your answer will be incorrect.

MIKE

http://www.skeptic.com/


Report •

#2
June 3, 2011 at 07:41:02
An "a" could be in X and Q , or in X and R, but there will never be an "a" in both Q and R, it will be in one or the other.

So this formula should work great. Thank you so much for your help.


Report •
Related Solutions


Ask Question