Countifs

[JP Ronse]

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

 

[T. Valko]

=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))

 

[Luke M]

Because it can't do that.

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

 

[JP Ronse]

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

With kind regards,

JP

 

[JP Ronse]

Hi Luke,

Thanks for your input.

With kind regards,

JP

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

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

With kind regards,

JP