Converting Sumifs to Excel 2003 [SOLVED]

[ejsw]

I'm trying to adjust an Excel 2007 workbook so that all of my formulas work with Excel 2003 and I've got one table has me stymied. I've tried a number of SUMPRODUCT and SUM calculations but keep getting a value of "$0" returned. Here's my formula:


=SUMIFS('Data Tab'!S$6:S$3770,'Data Tab'!$P$6:$P$3770,"Aircraft",'Data Tab'!$N$6:$N$3770,"Engines",'Data Tab'!$C$6:$C$3770,"Bolts")


When running correctly it returns all of the values for "aircraft engine bolts" in my table. Anyone have any ideas on how to make this work in Excel 2003?

 

[Axim5]

The "SUMIFS" is exclusively for 2007 version it will not work in prior versions. Reason, previous versions of Excel acknowledge formula only when it says "SUMIF" and not "SUNIFS" note the difference of letter "S" in 2007 version. Try the following array formula in 2003 version (make sure when you enter the formula you have to do CTRL+SHIFT+ENTER)


={SUM(IF((Data Range="Aircraft")*(Data Range="Engines")*(Data Range="Bolts"),data range with $ values,0))}


Axim5

 

[ejsw]

Thanks for the help - that worked perfectly. I knew SUMIFS wouldn't work in Excel 2003, just couldn't get any of the other formulas I knew that would work to deliver the exact sum I wanted. I appreciate the help.