• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Converting Sumifs to Excel 2003 [SOLVED]


New Member
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?
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))}

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.