How do I do the same for this formula?

[pgarcia]

This is what I have now, =COUNTIF(J19:J31,">= 2")but how do I do the same
for it but like this?
=IF(AND(J2>=2,J2<120),Q2,0)

=COUNTIF(and(J19:J31,">= 2",J19:J31"<7")) does not work?

 

[Gary''s Student]

With more than one criteria, it is easier to use SUMPRODUCT()

 

[David Biddulph]

=COUNTIF(J19:J31,">= 2")-COUNTIF(J19:J31,">= 7")

 

[pgarcia]

This is what I typed but got the "#Value!" message.
=SUMPRODUCT(AND(J19:J30>3,J19:J30<7))

 

[bj]

try
=sumproduct(--(j19:j30>3),--(j19:j30<7))
the "--(" converts the logic true or false to a numeric 1 or 0

 

[JLatham]

The sum product formula for J19 through J31>= 2 and J19 through J31<120 would
look like this:
=SUMPRODUCT(--(J19:J31>=2),--(J1931<120))
with values of 3, 4, 5, 1, 121 and 119 it will give result of 4.

 

[pgarcia]

Thanks, that's what I was missing.

The sum product formula for J19 through J31>= 2 and J19 through J31<120 would
look like this:
=SUMPRODUCT(--(J19:J31>=2),--(J1931<120))
with values of 3, 4, 5, 1, 121 and 119 it will give result of 4.

 

[pgarcia]

Thanks, that's what I was missing.

try
=sumproduct(--(j19:j30>3),--(j19:j30<7))
the "--(" converts the logic true or false to a numeric 1 or 0

 

[pgarcia]

Like I said before, that worked great. Now, is it possible to reture a value
from a cell?
=sumproduct(--(j19:j30>3),--(j19:j30<7),E5)