IF Range

[dgavin]

Can you help with the following forumula.

If "B" appears in range D18-D39 and any result is less than 5 in B18-B39, then "BACK" if not then ""

 

[Rick Rothstein]

Put this formula in Row 18 of the column to display the "back" and copy it down as needed....

=IF(C18<>"b","",IF(SUMPRODUCT((B$18:B$39<5)*(D$18:D$39="b")),"back",""))

 

[dgavin]

Thank you seems to be working great.

One more amendment regarding any A's

can you add this into the formula.

If D18="A" then N18. I don't need to worry about anything being <5 like the B's.

Thanks

 

[Rick Rothstein]

Try this...

=IF(D18="a",N18,IF(D18<>"b","",IF(SUMPRODUCT((B$18:B$39<5)*(D$18:D$39="b")),"back","")))

By the way, I see I had a typo my first formula (corrected in the above formula)... I had used C18 by accident when I had meant D18.

 

[dgavin]

sorry Rick I made a mistake


If D18="A" then "BACK", again don't worry about anything being <5.

 

[Rick Rothstein]

Then we just change the N18 that was in error to "BACK" (which you now want)....

=IF(D18="a","BACK",IF(D18<>"b","",IF(SUMPRODUCT((B$18:B$39<5)*(D$18:D$39="b")),"BACK","")))