countif 2 criteria

[BNT1 via OfficeKB.com]

Hi

Struggling to get this to work, despite looking at the treads. Using excel
2003.
trying to count how many times 5710 in col G has "6" in Col H

col-G col-H
5710 --- 7
5710 ----6
5694 ----6
5678----3
5710 ---6

=SUMPRODUCT(--(G2:G6=5710),--(H2:H6=6))

Would like to add 5694 as well as 5710, is this possible and does if have to
be on the same sheet?
thanks in advance
brian

 

[Don Guillett]

try
=SUMPRODUCT(--(G2:G6={5710,5694}),--(H2:H6=6))

 

[T. Valko]

Would like to add 5694 as well as 5710, is this possible

Yes, here's one way...

Use cells to hold the criteria:

J2 = 5710
K2 = 5694
L2 = 6

=SUMPRODUCT(--(ISNUMBER(MATCH(G2:G6,J2:K2,0))),--(H2:H6=L2))

does if have to be on the same sheet?

Does what have to be on the same sheet? The formula?

No

With the criteria on the same sheet as the formula:

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!G2:G6,J2:K2,0))),--(Sheet1!H2:H6=L2))

 

[T. Valko]

=SUMPRODUCT(--(G2:G6={5710,5694}),--(H2:H6=6))

Can't use the double unary in that syntax.

=SUMPRODUCT((G2:G6={5710,5694})*(H2:H6=6))

--
Biff
Microsoft Excel MVP

try
=SUMPRODUCT(--(G2:G6={5710,5694}),--(H2:H6=6))

 

[BNT1 via OfficeKB.com]

wow

thank you both for the speedy response
Could not get Don's to work- got #Value! error. wonder what I am doing wrong

T.Valko worked perfect, using the second formula for identifying on different
sheet

regards

Would like to add 5694 as well as 5710, is this possible

Yes, here's one way...

Use cells to hold the criteria:

J2 = 5710
K2 = 5694
L2 = 6

=SUMPRODUCT(--(ISNUMBER(MATCH(G2:G6,J2:K2,0))),--(H2:H6=L2))

does if have to be on the same sheet?

Does what have to be on the same sheet? The formula?

No

With the criteria on the same sheet as the formula:

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!G2:G6,J2:K2,0))),--(Sheet1!H2:H6=L2))

Hi

[quoted text clipped - 16 lines]

thanks in advance
brian

 

[BNT1 via OfficeKB.com]

one last Q before you retire to bed ;-)
how to countif value of 6 (L2), for those not in range J2:K2 ?
regards

Would like to add 5694 as well as 5710, is this possible

Yes, here's one way...

Use cells to hold the criteria:

J2 = 5710
K2 = 5694
L2 = 6

=SUMPRODUCT(--(ISNUMBER(MATCH(G2:G6,J2:K2,0))),--(H2:H6=L2))

does if have to be on the same sheet?

Does what have to be on the same sheet? The formula?

No

With the criteria on the same sheet as the formula:

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!G2:G6,J2:K2,0))),--(Sheet1!H2:H6=L2))

Hi

[quoted text clipped - 16 lines]

thanks in advance
brian

 

[T. Valko]

If I undersatand what you want...

=SUMPRODUCT(--(ISNA(MATCH(G2:G6,J2:K2,0))),--(H2:H6=L2))

--
Biff
Microsoft Excel MVP

one last Q before you retire to bed ;-)
how to countif value of 6 (L2), for those not in range J2:K2 ?
regards

Would like to add 5694 as well as 5710, is this possible

Yes, here's one way...

Use cells to hold the criteria:

J2 = 5710
K2 = 5694
L2 = 6

=SUMPRODUCT(--(ISNUMBER(MATCH(G2:G6,J2:K2,0))),--(H2:H6=L2))

does if have to be on the same sheet?

Does what have to be on the same sheet? The formula?

No

With the criteria on the same sheet as the formula:

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!G2:G6,J2:K2,0))),--(Sheet1!H2:H6=L2))

Hi

[quoted text clipped - 16 lines]

thanks in advance
brian

 

[BNT1 via OfficeKB.com]

yep- thank you again

If I undersatand what you want...

=SUMPRODUCT(--(ISNA(MATCH(G2:G6,J2:K2,0))),--(H2:H6=L2))

one last Q before you retire to bed ;-)
how to countif value of 6 (L2), for those not in range J2:K2 ?

[quoted text clipped - 27 lines]

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

yep- thank you again

If I undersatand what you want...

=SUMPRODUCT(--(ISNA(MATCH(G2:G6,J2:K2,0))),--(H2:H6=L2))

one last Q before you retire to bed ;-)
how to countif value of 6 (L2), for those not in range J2:K2 ?

[quoted text clipped - 27 lines]

thanks in advance
brian

 

[Don Guillett]

Damn. The one time I didn't TEST


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

=SUMPRODUCT(--(G2:G6={5710,5694}),--(H2:H6=6))

Can't use the double unary in that syntax.

=SUMPRODUCT((G2:G6={5710,5694})*(H2:H6=6))