Excel 2004: SUMIF error

[David M. Marcovitz]

Excel 2004.
Mac OS X 10.4

I am getting inconsistent and incorrect results using SUMIF.
I have a spreadsheet with a column of dates. I use the MONTH function to
extract the month to a new column. I then use other formulas to extract
a dollar figure to a new column so I end up with a column of months with
dollar figures next to it. I then want to add up the dollar amount for
each month. There are a couple of hundred rows so it looks something
like this:

1 $25.36
2 $13.28
1 $12.22
1 $17.65
2 $10.97
3 $100.00
4 $12.52
2 $7.44
4 $1.33
3 $9.22

etc.

This should be easy for SUMIF, and it works perfectly in Windows, but I
am not getting correct results on the Mac. I have created a little table
for the months and the totals for each month, so the formula looks
something like this:

=SUMIF(S$4:S$1000,U2,T$4:T$1000)

This should look at the values in column S, check to see if they are
equal to the value in cell U2 (that's my little table with the number of
the month, so as I fill down, that becomes 1 then 2 then 3 ... then 12
for each month). Right now, my March numbers show $3 even though there
are a few hundred dollars for March in column T, and my April number is
short about $25 (which is even more frustrating because it looks like a
reasonable number but isn't).

As I said, this works perfectly in Windows Excel, but it seems to be a
bug in Mac. I'm sure there are lots of other ways to do this, but this
way should work. Any thoughts as to why it doesn't?

--David

 

[Bob Greenblatt]

David,

Can you send me an example sheet that does not work properly? If there is
proprietary data, just send me the 2 columns with the month and the amount.

 

[JE McGimpsey]

As I said, this works perfectly in Windows Excel, but it seems to be a
bug in Mac.

The same workbook calculates different results in WinXL and MacXL?

I've never seen this where there are no errors in the data itself (under
some circumstances, SUMPRODUCT() returns somewhat different results in
MacXL and WinXL97 than does WinXL00/02/03/07 when there are XL errors in
the data).

By any chance are any of the amounts entered as Text?

 

[David M. Marcovitz]

The same workbook calculates different results in WinXL and MacXL?

I've never seen this where there are no errors in the data itself
(under some circumstances, SUMPRODUCT() returns somewhat different
results in MacXL and WinXL97 than does WinXL00/02/03/07 when there are
XL errors in the data).

By any chance are any of the amounts entered as Text?

The data that SUMIF is working with is all based on calculations (one
column comes from MONTH, and the other comes from a slightly complicated
IF statement to determine if anything belongs in that slot). I have only
tried this with Excel 2004 and Excel 2003 (running on a MacIntel), as
well as a calculator. When I opened the SS in 2003, the results matched
my calculator. When I opened the SS in 2004, they didn't. I would prefer
to keep this in 2004 as I rarely open Windows on that machine

Now, you ask if any of the amounts are text. Yes and no. Some of the
amounts are blank, and the blank is based on an IF formula. I would
assume that SUMIF would just ignore the blanks or treat them as 0.

--David

 

[David M. Marcovitz]

Thanks to everyone for their help. I figured it out, but I still think
it is a bug in MacXL. I don't have 2008, so I don't know what will
happen there. I don't have the original file here in my office, but I
threw something together. My column of dollar amounts included some
blanks (calculated blanks with code like:

=IF(OR(A2="sales",A2="wholesale"),C2,"")

If I change the "" to 0 (not "0"), then the formulas seem to work (as it
does in WinXL).

--David

 

[JE McGimpsey]

The data that SUMIF is working with is all based on calculations (one
column comes from MONTH, and the other comes from a slightly complicated
IF statement to determine if anything belongs in that slot). I have only
tried this with Excel 2004 and Excel 2003 (running on a MacIntel), as
well as a calculator. When I opened the SS in 2003, the results matched
my calculator. When I opened the SS in 2004, they didn't. I would prefer
to keep this in 2004 as I rarely open Windows on that machine

Now, you ask if any of the amounts are text. Yes and no. Some of the
amounts are blank, and the blank is based on an IF formula. I would
assume that SUMIF would just ignore the blanks or treat them as 0.

Yes, the blanks should be ignored. I'll echo Bob's offer. If you can
send me a copy of the workbook, I'll take a look. I've certainly never
seen the kind of error you're describing.