# 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

#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.MIKEhttp://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