SUMIFS Compatiblilty for Excel 2000

[Freddy]

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

 

[Pete_UK]

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

 

[Freddy]

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

 

[Freddy]

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)

 

[Pete_UK]

Glad to hear it - thanks for giving the details of SUMIFS.

Pete