Good morning,

I have the below formula which appears to work fine in 2007 but not with
earlier versions of excel.

Could someone please advise?

=SUMPRODUCT((JAN!G:G=B18)*(JAN!M:M="y")*(JAN!B:B=C 2))

When I save the document excel normally warns that certain formulas won't
work, however, it seems to think the above is OK, but thinks
=COUNTIFS(JAN!G:G,B14,JAN!B:B,C2) isn't,,, yet this one works fine in other
versions.

Thanks in anticipation,

Kind regards

Aaron

Hi,

Sumproduct can't use full columns in 2003 so ammend to this

=SUMPRODUCT((Jan!G1:G65535=B18)*(Jan!M1:M65535="y" )*(Jan!B1:B65535=C2))

Despite what you believe countifs is a 2007 function and won't work in 2003
or earlier, you would need an equivalent but because I'm not familiar with
this function I won't comment further.

Mike

Mike

"Aaron Hodson (Coversure)" wrote:

Good morning,

I have the below formula which appears to work fine in 2007 but not with
earlier versions of excel.

Could someone please advise?

=SUMPRODUCT((JAN!G:G=B18)*(JAN!M:M="y")*(JAN!B:B=C 2))

When I save the document excel normally warns that certain formulas won't
work, however, it seems to think the above is OK, but thinks
=COUNTIFS(JAN!G:G,B14,JAN!B:B,C2) isn't,,, yet this one works fine in other
versions.

Thanks in anticipation,

Kind regards

Aaron

Hi Aaron,

In versions before 2007, you can´t use SUMPRODUCT on a whole column.
And COUNTIFS (including the S) wasn´t invented yet before 2007.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Aaron Hodson (Coversure)" wrote in message
...
Good morning,

I have the below formula which appears to work fine in 2007 but not with
earlier versions of excel.

Could someone please advise?

=SUMPRODUCT((JAN!G:G=B18)*(JAN!M:M="y")*(JAN!B:B=C 2))

When I save the document excel normally warns that certain formulas won't
work, however, it seems to think the above is OK, but thinks
=COUNTIFS(JAN!G:G,B14,JAN!B:B,C2) isn't,,, yet this one works fine in
other versions.

Thanks in anticipation,

Kind regards

Aaron

COUNTIFS is similar to SUMPRODUCT but not *exactly* the same. With
SUMPRODUCT you can "manipulate" the arrays:

=SUMPRODUCT(--(MONTH(A1:A10)=5),--(B1:B10100))

We're testing ("manipulating") the array for the month number. You can't do
that with COUNTIFS. It only handles "straight" comparisons.

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Hi,

Sumproduct can't use full columns in 2003 so ammend to this

=SUMPRODUCT((Jan!G1:G65535=B18)*(Jan!M1:M65535="y" )*(Jan!B1:B65535=C2))

Despite what you believe countifs is a 2007 function and won't work in
2003
or earlier, you would need an equivalent but because I'm not familiar with
this function I won't comment further.

Mike

Mike

"Aaron Hodson (Coversure)" wrote:

Good morning,

I have the below formula which appears to work fine in 2007 but not with
earlier versions of excel.

Could someone please advise?

=SUMPRODUCT((JAN!G:G=B18)*(JAN!M:M="y")*(JAN!B:B=C 2))

When I save the document excel normally warns that certain formulas won't
work, however, it seems to think the above is OK, but thinks
=COUNTIFS(JAN!G:G,B14,JAN!B:B,C2) isn't,,, yet this one works fine in
other
versions.

Thanks in anticipation,

Kind regards

Aaron

Big thank to the replies,

Worked perfectly,

Thanks all,

Aaron

"Niek Otten" wrote in message
...
Hi Aaron,

In versions before 2007, you can´t use SUMPRODUCT on a whole column.
And COUNTIFS (including the S) wasn´t invented yet before 2007.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Aaron Hodson (Coversure)" wrote in message
...
Good morning,

I have the below formula which appears to work fine in 2007 but not with
earlier versions of excel.

Could someone please advise?

=SUMPRODUCT((JAN!G:G=B18)*(JAN!M:M="y")*(JAN!B:B=C 2))

When I save the document excel normally warns that certain formulas won't
work, however, it seems to think the above is OK, but thinks
=COUNTIFS(JAN!G:G,B14,JAN!B:B,C2) isn't,,, yet this one works fine in
other versions.

Thanks in anticipation,

Kind regards

Aaron

Big thank to the replies,

Worked perfectly,

Thanks all,

Aaron

"Niek Otten" wrote in message
...
Hi Aaron,

In versions before 2007, you can´t use SUMPRODUCT on a whole column.
And COUNTIFS (including the S) wasn´t invented yet before 2007.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Aaron Hodson (Coversure)" wrote in message
...
Good morning,

I have the below formula which appears to work fine in 2007 but not with
earlier versions of excel.

Could someone please advise?

=SUMPRODUCT((JAN!G:G=B18)*(JAN!M:M="y")*(JAN!B:B=C 2))

When I save the document excel normally warns that certain formulas won't
work, however, it seems to think the above is OK, but thinks
=COUNTIFS(JAN!G:G,B14,JAN!B:B,C2) isn't,,, yet this one works fine in
other versions.

Thanks in anticipation,

Kind regards

Aaron