converting a SUMIFS formula to work in 2003

[slp11]

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:


=IF(SUMIFS(codes!$D$2:$D$10870,codes!$C$2:$C$10870,$D16,codes!$A$2:$A$10870,G$2)=0,"",SUMIFS(codes!$D$2:$D$10870,codes!$C$2:$C$10870,$D16,codes!$A$2:$A$10870,G$2))


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

 

[vinu]

Hello Frnd,


You can try the below function in 2003:


=IF(SUMproduct((codes!$C$2:$C$10870=$D16)*(codes!$A$2:$A$10870=G$2)*(codes!$D$2:$D$10870))=0,"",SUMproduct((codes!$C$2:$C$10870=$D16)*(codes!$A$2:$A$10870=G$2)*(codes!$D$2:$D$10870)))


Hope this should work for you!!


Regards,

Vinu.

 

[slp11]

Thanks so much! I tried the formula but it is still not working. Is there maybe a better way write the formula?


To give you an understanding of what i am trying to do - I have a list of 25 different stores and each store has 150 items in it - each of the items having its own unique stock code for that store. I want a person to be able to select the store and the item and I want the stock code to automatically populate the cell.


Any help will be most appreciated!

Sandee

 

[keymaster]

Vinu's formula should work... try adding - - s like this:


=IF(SUMproduct(- -(codes!$C$2:$C$10870=$D16),- -(codes!$A$2:$A$10870=G$2),(codes!$D$2:$D$10870))=0,"",SUMproduct(- -(codes!$C$2:$C$10870=$D16),- -(codes!$A$2:$A$10870=G$2),(codes!$D$2:$D$10870)))

 

[Hui]

Sandee

Just a follow up comment


Using Vinu/Chandoos formula will give the right result but what it is doing is adding 10868 x 10868 x 10868 = 1.2 Billion calculations to each cell just to check if it is zero and then another 1.2 Billion to do it again if it isn't zero, and there are 10868 cells to do that. I'll bet this spreadsheet is slow


I would suggest the following


=sumproduct((codes!$C$2:$C$10870=$D16)*(codes!$A$2:$A$10870=G$2),(codes!$D$2:$D$10870))


and then use a Number format like ###.#, -###.#, "" to hide the zero

It also makes it a lot easier to read


Note that I have got rid of the double negatives as the two bracketed parts will do that by default. You only need a double negative when you have only 1 set of conditional brackets.