Microsoft Office 2007 home and student

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.

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

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.

Ask Your Question

Weekly Poll