If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"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 |
#2
|
|||
|
|||
"countifs" convert to formula in XL2002
=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 |
#3
|
|||
|
|||
"countifs" convert to formula in XL2002
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 |
Thread Tools | |
Display Modes | |
|
|