How do I do the same for this formula?
 

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?

How do I do the same for this formula?
 

With more than one criteria, it is easier to use SUMPRODUCT()
--
Gary''s Student
gsnu200710


"pgarcia" wrote:

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?

How do I do the same for this formula?
 

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

"pgarcia" wrote in message
...
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?

How do I do the same for this formula?
 

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

"pgarcia" wrote:

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?

How do I do the same for this formula?
 

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

"pgarcia" wrote:

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

"pgarcia" wrote:

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?

How do I do the same for this formula?
 

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

"pgarcia" wrote:

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

"pgarcia" wrote:

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?

How do I do the same for this formula?
 

Thanks, that's what I was missing.

"JLatham" wrote:

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

"pgarcia" wrote:

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

"pgarcia" wrote:

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?

How do I do the same for this formula?
 

Thanks, that's what I was missing.

"bj" wrote:

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

"pgarcia" wrote:

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

"pgarcia" wrote:

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?

How do I do the same for this formula?
 

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

"bj" wrote:

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

"pgarcia" wrote:

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

"pgarcia" wrote:

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?