Excel 2007 : SUMIFS into Excel 2003

[Physicsboy]

I just realized I can't use SUMIFS in Excel 2003.

My formula is as follows (and works in 2007, but not in 2003)...

=SUMIFS(Sheet1!$B:$B,Sheet1!$A:$A,Orders!$B2,Sheet1!$C:$C,Orders!D$1)

I tried the following formula hoping it would work in 2003, but it only returns values of "0". My thought process is I need an AND statment finding both the Date and Part...THEN summing up the values for that specific date and specific part number.

=SUM(IF(AND(Sheet1!C:C=Orders!D7,Sheet1!E:E=Orders!F6),Sheet1!D:D,0))

[tigeravatar]

Physicsboy,

Give this a try:
=SUMPRODUCT((Sheet1!$A$2:$A$71=$B2)*(Sheet1!$C$2:$C$71=D$1)*Sheet1!$B$2:$B$71)

[Physicsboy]

Screenshot.jpgI thought this was working, but some results seem to be filtered??? Screenshot if you look in grey rows (top one is sumifs, bottom one is sumproduct)

I also had a question, for the range can I select an entire column of data? I originally tried that, but would not return correct values. I instead selected the specific range of data...and most of it worked, but again, my results are sometimes different.

[tigeravatar]

Sumproduct calculates like an array formula and as such it should never use an entire column (even if it does work, it bogs excel down pretty hard).
As for not getting the correct values, I'd have to see an example workbook that is experiencing the issue. Generally speaking though, the format for converting sumifs to sumproduct is:

=SUMPRODUCT((CriteriaRange1=Criteria1)*(CriteriaRange2=Criteria2)*SumRange)

[Physicsboy]

Sorry I posted screenshot of actual spreadsheet I'm working with...here is a comparison of syntax stated with results compared to sumifs. Am I missing something?

[tigeravatar]

Physicsboy,

I found the issue. Many of the dates on Sheet1 were text values and not actually dates. That was causing the sumproduct to calculate incorrectly. I converted them all to actual dates and now the sumproduct calculation is identical to the sumifs.

[Physicsboy]

Well, that works!! However...lol...I can't get it to work in my spreadsheet. I'm attaching it here...should have used it to start with. I'm assuming at this point it's a formatting issue, but I can't seem to find it. Look in the Orders tab, cell BV328...that sum total should be 4000 pcs. I'm thinking the formatting is in the parts, and/or dates somewhere. Thanks for all the help thus far...I'm almost there.

[tigeravatar]

Physicsboy,

I converted all dates in both worksheets to be actual dates and cell Orders!BV328 now = 4000.

[Physicsboy]

Tiger, I can't repeat your success. What was your process? I am highlighting all cells, right-click...change format of cells to date. I am assuming my process is wrong, as it has no effect on BV328???

[Physicsboy]

Tiger, I got it to work. I'm a little confused on they why it affected the results, but I changed the font size and type in Sheet1...and this gave me the result 4000!! Problem solved, but I'm still curious as to sumproduct function...dependent on all cells formatting (like if it's highlighted can that affect it, type of font, size of font, etc.). Thanks for all the help...really appreciate the persistence.

[tigeravatar]

Not sure why changing font formatting would have had an effect. The process I used to convert the dates to actual dates was:

1. Click a single blank cell and copy it
2. Select all cells containing dates
3. Right-click > Paste Special > select Add > OK

[Physicsboy]

For the love of Pete. What am I missing. Tiger, how did you change the formatting? I change to date, etc. But still 1396.

Edit -> Just tried solution mentioned above, and still same result (1396)

Edit2 -> Noticed I paste as special and select formatting. Is this where I'm going wrong, as I don't click the "add" button?

[tigeravatar]

Don't select formatting, select "Add" in the Operation section of the Paste Special dialog, and then click OK (see attached for screenshot)

[Physicsboy]

Wha...wha...WHAAAAAAAAAAAAAAAAT!! It worked in one easy step. Not even sure why formatting is messed up, it doesn't appear to be when I look at the cells. Either way it works...

Copy blank cell...paste special selecting add = MAGIC!!

Edit -> Thank you very much!