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.

a number greater than 200
=COUNTIF(D4:F19,"=200")

Try this:

=SUMPRODUCT((D4:F19=200)*(J4:J19=145))


--
Biff
Microsoft Excel MVP


"ref at heart" wrote in message
...
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!

--------------------
(Ms-Exl-Learner)
--------------------



"ref at heart" wrote:

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.

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
---------------
Jacob Skaria


"ref at heart" wrote:

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.

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

"Ms-Exl-Learner" wrote in message
...
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!

--------------------
(Ms-Exl-Learner)
--------------------



"ref at heart" wrote:

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.

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.



"T. Valko" wrote:

a number greater than 200
=COUNTIF(D4:F19,"=200")

Try this:

=SUMPRODUCT((D4:F19=200)*(J4:J19=145))


--
Biff
Microsoft Excel MVP


"ref at heart" wrote in message
...
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.



.

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
--
David Biddulph

"ref at heart" wrote in message
...
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.



"T. Valko" wrote:

a number greater than 200
=COUNTIF(D4:F19,"=200")

Try this:

=SUMPRODUCT((D4:F19=200)*(J4:J19=145))


--
Biff
Microsoft Excel MVP


"ref at heart" wrote in message
...
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.



.

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ref at heart" wrote in message
...
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.



"T. Valko" wrote:

a number greater than 200
=COUNTIF(D4:F19,"=200")

Try this:

=SUMPRODUCT((D4:F19=200)*(J4:J19=145))


--
Biff
Microsoft Excel MVP


"ref at heart" wrote in message
...
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.



.