SUMIFS Compatiblilty for Excel 2000

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have written the following function using Excel 2007

=SUMIFS(S2974_1!$K$30:$K$39, S2974_1!$F$30:$F$39, $B5, S2974_1!$L$30:$L$39,
C$3)

However, i have just found out that the recipients only have access to Excel
2000

When this function is run in the earlier version, I get a #name error

Does anyone know a workaround for this?

Thanks in advance
 
For those of us that don't have XL2007, perhaps you can explain what
SUMIFS does and what the syntax is, so we can advise how to do the
equivalent in earlier versions.

Pete
 
SUMIFS:

Adds the cells in a range that meet multiple criteria.

Syntax

SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)

Sum_range is one or more cells to sum, including numbers or names, arrays,
or references that contain numbers. Blank and text values are ignored.

Criteria_range1, criteria_range2, … are 1 to 127 ranges in which to
evaluate the associated criteria.

Criteria1, criteria2, … are 1 to 127 criteria in the form of a number,
expression, cell reference, or text that define which cells will be added.
For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

More information is available here:

http://office.microsoft.com/en-gb/excel/HA100475041033.aspx?pid=CH100645361033
 
Found a solution for this:

=SUMPRODUCT(S2974_1!$K$30:$K$39, (S2974_1!$F$30:$F$39=$B5)+0,
(S2974_1!$L$30:$L$39=C$3)+0)
 
Back
Top