Alternative solution to SUMIFS in Excel 2003?

**ThomasColombia** · 12-14-2011, 09:20 AM

Hi! I'm trying to make some statistics from a spreadsheet with several criteria; date, vendor, type of order, approved or not, and the value of a certain sale. It was all working great until I discovered that the document in which I was going to implement this stuff is an Excel 2003 document, and I had been working in 2007 using SUMIFS. SUMIFS doesn't exist in Excel 2003, and the document cannot be converted into a 2007 version because many people in the company are accessing this file, but they only have Excel 2003.

So, I need to find an Excel 2003 alternative to SUMIFS.

Here is a sample formula from what I'm working with: =SUMIFS(Sheet4!H:H;Sheet4!D:D;"COT";Sheet4!M:M;"X";Sheet4!B:B;"sep-2011")

Column H is the value which is summed up depending on the other criteria.

In addition, I'm working in a Spanish Excel so I don't if the signs, like ";" and ",", are using differently in an English Excel.

Any help would be greatly appreciated!

Thomas

**NBVC** · 12-14-2011, 09:32 AM

Try:

=SUMPRODUCT(Sheet4!$H$2:$H$1000;--(Sheet4!$D$2:$D$1000="COT");--(Sheet4!$M$2:$M$1000="X");--(Sheet4!$B$2:$B$1000="sep-2011"))

Note: With this function you cannot use whole column references... and also you should not make the ranges too large as this version is less efficient than SUMIFS.

**dilipandey** · 12-14-2011, 02:07 PM

Hi Thomas,

Try using below formula:-

=SUM(IF((Sheet4!D1:D20="COT")*(Sheet4!M1:M20="X")*(Sheet4!B1:B20="sep-2011"),Sheet4!H1:H20,""))

It assumes that you have your data spread from row 1 to 20, change the rows as per your data arrangement.

Let me know if this solves your problem.

Regards,
DILIPandey

<click on below 'star' if this helps >

**jpr73** · 12-14-2011, 07:41 PM

Hi,
There is a great tool for the formula translator in different language
See Excel Formula Translator by Mourad Louha powered by http://www.excel-translator.de/
Best regards

**dilipandey** · 12-14-2011, 10:11 PM

Nice link jpr73.

Regards,
DILIPandey

<click on below 'star' if this helps >

**mardyl** · 12-15-2011, 12:45 AM

I would create formulas in additional column for example AA (the column can be later hidden). In first cell of column AA would enter the formula:
<=IF(AND(B1="sep-2011",D1="COT",M1="x"),H1,0)> then copy formula to other cells in the same column and finally sum all up <=sum(AA1:AA??)>
in the cell you want to have it

**ThomasColombia** · 12-15-2011, 10:46 AM

Thanks a lot! That seems to work! I actually also need an alternative to COUNTIFS (completely forgot about that, but it should be ever more simple). Here goes:

=COUNTIFS(Sheet4!B:B;"sep-2011";Sheet4!D:D;"COT";Sheet4!M:M;"X")

I know about the ranges, and thanks for the advice on that. Basically, the formula should only count if all criteria are fulfilled. How can I do that?

**NBVC** · 12-15-2011, 10:54 AM

It's similar.. just leave out summing range:

=SUMPRODUCT(--(Sheet4!$D$2:$D$1000="COT");--(Sheet4!$M$2:$M$1000="X");--(Sheet4!$B$2:$B$1000="sep-2011"))

**ThomasColombia** · 12-15-2011, 10:58 AM

Originally Posted by NBVC

Try:

=SUMPRODUCT(Sheet4!$H$2:$H$1000;--(Sheet4!$D$2:$D$1000="COT");--(Sheet4!$M$2:$M$1000="X");--(Sheet4!$B$2:$B$1000="sep-2011"))

Note: With this function you cannot use whole column references... and also you should not make the ranges too large as this version is less efficient than SUMIFS.

Sorry for posting this twice, just couldn't figure out how to delete my first reply. But thanks a lot! That seems to work! I actually also need an alternative to COUNTIFS (completely forgot about that, but it should be ever more simple). Here goes:

=COUNTIFS(Sheet4!B:B;"sep-2011";Sheet4!D:D;"COT";Sheet4!M:M;"X")

I know about the ranges, and thanks for the advice on that. Basically, the formula should only count if all criteria are fulfilled. How can I do that?