i need to make the following backwards compatable:
=SUMIFS($S:$S,$F:$F,$AI8,AM:AM,"<0")

Try this. It'll work in all versions of Excel.

=SUMPRODUCT(--($F1:$F100=$AI8),--(AM1:AM100<0),$S1:$S100)

Note that with the SUMPRODUCT function you *can't* use entire columns as
range references in Excel versions prior to Excel 2007.


--
Biff
Microsoft Excel MVP


"jam" wrote in message
...
shane,

I would like to know how you would replace =SUMIFS (and for that matter
the
other 3). Our orgenisation has a mixture of excel versions atm and i
encounter problems like this often. If you have found a rescource that
describes these sort of work arounds that would be great.

as an example, at the moment i need to make the following backwards
compatable:

=SUMIFS($S:$S,$F:$F,$AI8,AM:AM,"<0")

thanks in advance for your help

Jam

"Shane Devenshire" wrote:

Hi,

The following function are new in 2007 and therefore not supported in
2003:

=SUMIFS
=COUNTIFS
=AVERAGEIF
=AVERAGEIFS
=IFERROR
and 8 cube functions.

You can duplicate the behavior of the first 4 without too much problem
using
SUMPRODUCT or other function compatible to 2003, but the last ones would
probalby require VBA
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Steve" wrote:

I created some countifs in 2007, saved it down to 2003. When opened in
2003,
in front of the countifs is: =_xlfn.COUNTIFS

It showed correctly in 2003, however, when I added another number in
the
range that the countifs were counting, an #name? error was produced,
and it
couldn't be undone. Is there any way around this ? Though I'm still
impressed
that 2003 was able to keep the 2007 results to begin with. What does
xlfn
mean ?

Thanks,


Steve