Countifs
in [Functions]
|
From: JP Ronse on 7 Apr 2010 11:39 Hi All, New in Excel 2007. Why is Countifs giving an error when I try to enter it with combined formulas like: =countifs(month(range);1;other_range;1) With kind regards, JP
From: T. Valko on 7 Apr 2010 11:51 >=countifs(month(range);1;other_range;1) SUMIF SUMIFS COUNTIF COUNTIFS AVERAGEIF AVERAGEIFS These functions can only handle "straight" comparisons. That is, you can't manipulate a range array to test for a condition. In the formula above you're trying to manipulate the range array by first testing for the month. MONTH(range) = 1 The test has to be a "straight" comparison: range = 1 Of course, that doesn't do what you want so you need to use a different function. =SUMPRODUCT(--(MONTH(range)=1),--(other_range=1)) -- Biff Microsoft Excel MVP "JP Ronse" <fb893760(a)skynet.be> wrote in message news:%23Mue6hm1KHA.4832(a)TK2MSFTNGP04.phx.gbl... > Hi All, > > New in Excel 2007. > > Why is Countifs giving an error when I try to enter it with combined > formulas like: > > =countifs(month(range);1;other_range;1) > > With kind regards, > > JP >
From: Luke M on 7 Apr 2010 11:52 Because it can't do that. Try: =SUMPRODUCT(--(MONTH(range)=1),--(other_range=1)) -- Best Regards, Luke M "JP Ronse" <fb893760(a)skynet.be> wrote in message news:%23Mue6hm1KHA.4832(a)TK2MSFTNGP04.phx.gbl... > Hi All, > > New in Excel 2007. > > Why is Countifs giving an error when I try to enter it with combined > formulas like: > > =countifs(month(range);1;other_range;1) > > With kind regards, > > JP >
From: JP Ronse on 8 Apr 2010 05:22 Thanks for the feedback. And yes, I realized later on my mistake. With kind regards, JP "T. Valko" <biffinpitt(a)comcast.net> wrote in message news:%23IYbjom1KHA.776(a)TK2MSFTNGP04.phx.gbl... > >=countifs(month(range);1;other_range;1) > > SUMIF > SUMIFS > COUNTIF > COUNTIFS > AVERAGEIF > AVERAGEIFS > > These functions can only handle "straight" comparisons. That is, you can't > manipulate a range array to test for a condition. > > In the formula above you're trying to manipulate the range array by first > testing for the month. > > MONTH(range) = 1 > > The test has to be a "straight" comparison: > > range = 1 > > Of course, that doesn't do what you want so you need to use a different > function. > > =SUMPRODUCT(--(MONTH(range)=1),--(other_range=1)) > > -- > Biff > Microsoft Excel MVP > > > "JP Ronse" <fb893760(a)skynet.be> wrote in message > news:%23Mue6hm1KHA.4832(a)TK2MSFTNGP04.phx.gbl... >> Hi All, >> >> New in Excel 2007. >> >> Why is Countifs giving an error when I try to enter it with combined >> formulas like: >> >> =countifs(month(range);1;other_range;1) >> >> With kind regards, >> >> JP >> > >
From: JP Ronse on 8 Apr 2010 05:22 Hi Luke, Thanks for your input. With kind regards, JP "Luke M" <lukemoraga(a)nospam.com> wrote in message news:OF4pSpm1KHA.4832(a)TK2MSFTNGP04.phx.gbl... > Because it can't do that. > > Try: > =SUMPRODUCT(--(MONTH(range)=1),--(other_range=1)) > > -- > Best Regards, > > Luke M > "JP Ronse" <fb893760(a)skynet.be> wrote in message > news:%23Mue6hm1KHA.4832(a)TK2MSFTNGP04.phx.gbl... >> Hi All, >> >> New in Excel 2007. >> >> Why is Countifs giving an error when I try to enter it with combined >> formulas like: >> >> =countifs(month(range);1;other_range;1) >> >> With kind regards, >> >> JP >> > >
|
Next
|
Last
Pages: 1 2 Prev: editing a hyperlink Next: What is the most straightforward (elegant) way to write this formu |