"countifs" convert to formula in XL2002

Hi,

xl2007 formula "COUNTIFS" as following:-

=COUNTIFS('MTD Reach'!K:K,"0",'MTD Reach'!A:A,B12:B277)

*Criteria range 1 = 'MTD Reach' (Sales data)
*Criteria 1 = 0 (More than 0)
*Criteria range 2 = 'MTD Reach'!A:A (Range of source data)
*Criteria 2 = B12:B277(Data Match)

how to convert it to suit xl2000-2003 version

Regards

Alan

=SUMPRODUCT(--(K2:K2000),--(ISNUMBER(MATCH(A2:A200,B12:B277,0))))

Note that SUMPRODUCT doesn't work with complete columns pre Excel 2007, you
have to specify a range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Alan" wrote in message
...
Hi,

xl2007 formula "COUNTIFS" as following:-

=COUNTIFS('MTD Reach'!K:K,"0",'MTD Reach'!A:A,B12:B277)

*Criteria range 1 = 'MTD Reach' (Sales data)
*Criteria 1 = 0 (More than 0)
*Criteria range 2 = 'MTD Reach'!A:A (Range of source data)
*Criteria 2 = B12:B277(Data Match)

how to convert it to suit xl2000-2003 version

Regards

Alan

Hi Bob,

thanks, is work

=SUMPRODUCT(--('MTD Reach'!K2:K12930),--(ISNUMBER(MATCH('MTD
Reach'!A2:A1293,B12,0))))

thank you very much..

Alan

"Bob Phillips" wrote:


=SUMPRODUCT(--(K2:K2000),--(ISNUMBER(MATCH(A2:A200,B12:B277,0))))

Note that SUMPRODUCT doesn't work with complete columns pre Excel 2007, you
have to specify a range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Alan" wrote in message
...
Hi,

xl2007 formula "COUNTIFS" as following:-

=COUNTIFS('MTD Reach'!K:K,"0",'MTD Reach'!A:A,B12:B277)

*Criteria range 1 = 'MTD Reach' (Sales data)
*Criteria 1 = 0 (More than 0)
*Criteria range 2 = 'MTD Reach'!A:A (Range of source data)
*Criteria 2 = B12:B277(Data Match)

how to convert it to suit xl2000-2003 version

Regards

Alan