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

T. Valko wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/201003/1