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

>=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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>

Because it can't do that.

Try:
=SUMPRODUCT(--(MONTH(range)=1),--(other_range=1))

--
Best Regards,

Luke M
"JP Ronse" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>

Thanks for the feedback. And yes, I realized later on my mistake.

With kind regards,

JP


"T. Valko" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> >=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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>
>

Hi Luke,

Thanks for your input.

With kind regards,

JP

"Luke M" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Because it can't do that.
>
> Try:
> =SUMPRODUCT(--(MONTH(range)=1),--(other_range=1))
>
> --
> Best Regards,
>
> Luke M
> "JP Ronse" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>
>

You're welcome!

--
Biff
Microsoft Excel MVP


"JP Ronse" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks for the feedback. And yes, I realized later on my mistake.
>
> With kind regards,
>
> JP
>
>
> "T. Valko" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> >=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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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
>>>
>>
>>
>
>