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

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History