SUMPRODUCT in earlier verions

[Aaron Hodson \(Coversure\)]

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

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

 

[Mike H]

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

 

[Niek Otten]

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.

 

[T. Valko]

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

=SUMPRODUCT(--(MONTH(A1:A10)=5),--(B1:B10>100))

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

 

[Aaron Hodson \(Coversure\)]

Big thank to the replies,

Worked perfectly,

Thanks all,

Aaron

 

[Aaron Hodson \(Coversure\)]

Big thank to the replies,

Worked perfectly,

Thanks all,

Aaron