SUMPRODUCT in earlier verions

  • Thread starter Aaron Hodson \(Coversure\)
  • Start date
A

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
 
M

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
 
N

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top