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 ""

 

[GlennUK]

Do you mean any corresponding result is less than 5?

 

[dgavin]

yes but....

Basically I want to return "BACK" if a B is found in range D18-D39, but only if at least ONE of the corresponding D cells is less than 5.

If none of the D cells is less than 5 then the formula should return ""

 

[T. Valko]

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 ""

One way...

=IF(SUMPRODUCT(--(D18:D39="B"),--(B18:B39<5)),"BACK","")

 

[T. Valko]

One way...

=IF(SUMPRODUCT(--(D18:D39="B"),--(B18:B39<5)),"BACK","")

If you're using Excel 2007 or later then you can use the more efficient COUNTIFS function.

=IF(COUNTIFS(D18:D39,"B",B18:B39,"<5"),"BACK","")

 

[Rick Rothstein]

Here is another way that should work in any version of Excel...

=IF(COUNTIF(B18:B39,"<5")+COUNTIF(D18:D39,"B")>1,"BACK","")

 

[T. Valko]

yes but....

Basically I want to return "BACK" if a B is found in range D18-D39, but only if at least ONE of the corresponding D cells is less than 5.

If none of the D cells is less than 5 then the formula should return ""

OK, now I'm confused!

Do the conditions have to be on the same row? Like this:

Book1
	B	C	D
2	100	_	A
3	100	_	D
4	2	_	B
5	300	_	M
Sheet1

Or, can the conditions be on ANY rows like this:

Book1
	B	C	D
2	100	_	B
3	100	_	D
4	100	_	A
5	2	_	M
Sheet1

 

[dgavin]

20 a
15 a
4 b back
9 b back
6 a
18 a
20 b back

Hope you can understand above!

Yes the conditions must be on the same row.

only one cell in column B has dropped below 5 (4) and it has a corresponding "B" in column D, but this is enough for all the "B"'s in column D to give a "BACK" result.

 

[Rick Rothstein]

20 a
15 a
4 b back
9 b back
6 a
18 a
20 b back

Hope you can understand above!

Yes the conditions must be on the same row.

only one cell in column B has dropped below 5 (4) and it has a corresponding "B" in column D, but this is enough for all the "B"'s in column D to give a "BACK" result.

Okay, this raises another question. What if one of the "a" rows had dipped below 5... would you want all the "a" rows to be marked "back", or is this always only for "b" rows?

 

[dgavin]

This is only for "B" rows

Thanks