Double Condition (If w/nested sumif)

[schmadly]

The Issue: I need a formula that looks for an "x" in cell value I21. If it finds an "x", then I need it to see if there is either a "y" or a "p" in the cell directly above. If there is a "y" or a "p", then I need it to find the number above the "y" or the "p" and sum all of those number in one cell.

What I have tried: An IF with a SUMIF nested inside, but it would only sum ALL numbers, I didn't get it to look for double criteria (both the "x" and the "y"/"p" above the "x".

Example: The answer should be "2" (the first column has a "y" and an "x" so it finds the "1" above them - the second column has a "p" and an "x" so it finds that "1" above them and then adds the two 1's together). Make sense?



1 1 2 2 1
Y P Y N P
x x

 

[Brian from Maui]

Per your example, try;

=SUMPRODUCT(--(A3:F3="x"),--(ISNUMBER(MATCH(A2:F2,{"p","y"},0))),A1:F1)

 

[schmadly]

Thank you very much

It works! Thanks a bunch - I had forgotten about SUMPRODUCT. I know about MATCH, but what is the purpose of the --ISNUMBER?

 

[Brian from Maui]

Re: Thank you very much

It works! Thanks a bunch - I had forgotten about SUMPRODUCT. I know about MATCH, but what is the purpose of the --ISNUMBER?

My understanding (corrections welcome) is, ISNUMBER tests whether a match is found and does not return the #N/A error if there is no match.