Countifs
 

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
 

=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" wrote in message
...
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
 

Because it can't do that.

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

--
Best Regards,

Luke M
"JP Ronse" wrote in message
...
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
 

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

With kind regards,

JP


"T. Valko" wrote in message
...
=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" wrote in message
...
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
 

Hi Luke,

Thanks for your input.

With kind regards,

JP

"Luke M" wrote in message
...
Because it can't do that.

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

--
Best Regards,

Luke M
"JP Ronse" wrote in message
...
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
 

You're welcome!

--
Biff
Microsoft Excel MVP


"JP Ronse" wrote in message
...
Thanks for the feedback. And yes, I realized later on my mistake.

With kind regards,

JP


"T. Valko" wrote in message
...
=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" wrote in message
...
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