Countif, plus ?

  • Thread starter Thread starter ref at heart
  • Start date Start date
R

ref at heart

Hello all,
What I need to do is when the formula below finds a number greater than 200
it needs to look in the corresponding row in the J column and verify that it
is greater than or equal to 145.

=COUNTIF(D4:F19,">=200")

D E F J
167 203 159 176
171 199 151 177

Don't believe you need the other information ~ but just in case.
Column G = D4+E4+F4
Column H = running total of colum G
Column I = =COUNTIF(D4:F4,">0")
Column J = =ROUNDDOWN(H4/I4,0)

thank you.
 
I am not clear what you are asking for... Anyway try the below.

If you want perform the formula when the D2 is ">=200", E2 is ">=200" & F2
is ">=200" then use this...

=IF(AND(D2>=200,E2>=200,F2>=200),IF(J2>=145,J2),"")

OR

If you want to total the D2, E2 & F2 values and want to check whether it is
">=200" then use this...

=IF(SUM(D2:F2)>=200,IF(J2>=145,J2),"")

If this post helps, Click Yes!
 
The below formula check whether there is any number in the row which is above
199. If so it will check whether the number is above 145....

=IF(COUNTIF(D4:F4,">=200"),IF(J4>=145,"Greater than 145","Less than 145"),
"No > 200 nos")

If this post helps click Yes
 
If you want to use one of those formulae, you may wish to specify an
alternative outcome in the second IF statement in each formula, as otherwise
the formula will return the boolean value false FALSE when J2 is < 145 if
the first condition is met.

If the alternative outcome is again to be an empty string, you could change
=IF(AND(D2>=200,E2>=200,F2>=200),IF(J2>=145,J2),"")
to
=IF(AND(D2>=200,E2>=200,F2>=200,J2>=145),J2,"")
and change
=IF(SUM(D2:F2)>=200,IF(J2>=145,J2),"")
to
=IF(AND(SUM(D2:F2)>=200,J2>=145),J2,"")

It is, as you say, not clear what the OP wanted.
 
If you want to use one of those formulae, you may wish to specify an
alternative outcome in the second IF statement in each formula, as otherwise
the formula will return the boolean value false FALSE when J2 is < 145 if
the first condition is met.

If the alternative outcome is again to be an empty string, you could change
=IF(AND(D2>=200,E2>=200,F2>=200),IF(J2>=145,J2),"")
to
=IF(AND(D2>=200,E2>=200,F2>=200,J2>=145),J2,"")
and change
=IF(SUM(D2:F2)>=200,IF(J2>=145,J2),"")
to
=IF(AND(SUM(D2:F2)>=200,J2>=145),J2,"")

It is, as you say, not clear what the OP wanted.
--
David Biddulph







- Show quoted text -
Click to expand...

Thank you David Sir, Learning lot of valuable informations from
experts like you...
 
Thank you T. Valko,
I thought my question was clear with the countif D1:F19 formula
I just don't understand how a " * " multiple symbol works in your formula,
yet I have never used a sumproduct function before.
Again thank you, one more step closer to my bowling spreadsheet.
 
If you try to do an artithmetic operation on a boolean TRUE or FALSE, it
will be treated as 1 or 0 respectively.

A multiply operation is therefore effectively an AND function:
=1*1 is 1 just as =AND(TRUE,TRUE) is TRUE
=1*0 is 0 just as =AND(TRUE,FALSE) is FALSE
=0*1 is 0 just as =AND(FALSE,TRUE) is FALSE
=0*0 is 0 just as =AND(FALSE,FALSE) is FALSE
 
Back
Top