Excel 2008: Sumifs Error

R

RyeDarrow

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I created a spreadsheet in Office 2007 to help me track my college expenses.
Column A "Date" B "Category" C "Amount"

The Formula I created in 2007 to calculate my expenses for each Category per Month is:

=SUMIFS($C$5:$C$300,$B$5:$B$300,"books",$A$5:$A$300,">=9/1/2008",$A$5:$A$300,"<10/1/2008")

When I opened the document in 2008 it now reads #VALUE...?

After doing some research online I tried adjusting the formula to:
=SUMIFS($C$5:$C$300,$B$5:$B$300,"books",$A$5:$A$300,">="&"9/1/2008",$A$5:$A$300,"<"&"10/1/2008")

I cant understand why it works in 2007 on PC, but 2008 cant figure it out on my Mac? I know 2008 doesn't have VB support, but that shouldnt be a problem for a sumifs formula?

I have been at it for 3 days now, any help would be greatly appreciated. Thanks guys.

-Ryan
 
B

Bob Greenblatt

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I created a spreadsheet in Office 2007 to help me track my college expenses.
Column A "Date" B "Category" C "Amount"

The Formula I created in 2007 to calculate my expenses for each Category per
Month is:

=SUMIFS($C$5:$C$300,$B$5:$B$300,"books",$A$5:$A$300,">=9/1/2008",$A$5:$A$300,"
<10/1/2008")

When I opened the document in 2008 it now reads #VALUE...?

After doing some research online I tried adjusting the formula to:
=SUMIFS($C$5:$C$300,$B$5:$B$300,"books",$A$5:$A$300,">="&"9/1/2008",$A$5:$A$30
0,"<"&"10/1/2008")

I cant understand why it works in 2007 on PC, but 2008 cant figure it out on
my Mac? I know 2008 doesn't have VB support, but that shouldnt be a problem
for a sumifs formula?

I have been at it for 3 days now, any help would be greatly appreciated.
Thanks guys.

-Ryan
Click to expand...
Looks like a bug to me. Maybe someone else will verify.
 
R

Ryan

It has to be a bug, Office '08 cant seem to calculate dates within sumif. It works flawlessly in Office '07 with VMware. This is very frustrating.

Any other formula ideas? I hate having VMware open all the time.
 
J

Joe_LeBlanc

I created a spreadsheet in Office 2007 to help me track my college expenses.
Column A "Date" B "Category" C "Amount"

The Formula I created in 2007 to calculate my expenses for each Category per Month is:

=SUMIFS($C$5:$C$300,$B$5:$B$300,"books",$A$5:$A$300,">=9/1/2008",$A$5:$A$300,"<10/1/2008")

When I opened the document in 2008 it now reads #VALUE...?

After doing some research online I tried adjusting the formula to:
=SUMIFS($C$5:$C$300,$B$5:$B$300,"books",$A$5:$A$300,">="&"9/1/2008",$A$5:$A$300,"<"&"10/1/2008")

I cant understand why it works in 2007 on PC, but 2008 cant figure it out on my Mac? I know 2008 doesn't have VB support, but that shouldnt be a problem for a sumifs formula?

I have been at it for 3 days now, any help would be greatly appreciated. Thanks guys.

-Ryan
Click to expand...

Hi Ryan,

Thanks for the report, I'm looking into it. If possible, could you send me a copy of the file you were working with, or a similar one.

Thank you!

-joe leblanc
(e-mail address removed)
MacBU Excel Team
 
E

Evan maxon

I'm wanting to do something very similar and would like to know how I can be updated on the status of this issue.

Instead of entering into the formula a specific date range, I am wanting it to pull dates from the current month, then match another column of categories (for the transactions), and then sum the third column.

I, too, am getting the #VALUE error.

Appreciate any insight that may be offered.
 
R

RyeDarrow

Evan,

I sent my spreadsheet to Joe, and he and I both worked on it together. I was getting a #value error and finally fixed it.

There is some kind of bug and by changing the ranges of the cells to 5:30 instead of 5:300 it suddenly started working. Then, by moving the whole spreadsheet up one row, I changed the cell range back to 5:300 and it suddenly worked. Very weird.

My advice is to open it in Office '07 and make sure you have your formula correct. Mine worked in Windows but not on my Mac. That's how I determined it was a bug.

I'd be happy to take a look at it if you want?
I am not as familiar with your formula because I don't like or have much experience using date() month() etc., but I will give it my best shot.

Good luck,
Ryan
(e-mail address removed)
 
P

Pat McMillan

Yes. We're actually investigating a fix for this now. I can't say exactly
when we will be able to release an update with a fix, but we recognize that
this is an important issue and will do our best to get a fix out soon.

Thanks,

Pat
 
K

KimW

I am having a problem with the SUMIFS formula as well--but I am not using dates. Do you think I am having the same problem? I work with Excel 2007 for Windows, and my client who has Excel 2008 for Mac gets #VALUE in all the cells with this formula. What should he do?

=SUMIFS('Data - Data Information'!$E:$E,'Data - Data Information'!$I:$I,"FAROE ISLANDS",'Data - Data Information'!$J:$J,"BL201")

Is there another formula I can use in place of this one?

Thanks in advance.
 
B

Ben

Where is the fix?!

Thanks!

Pat McMillan said:
Great. I'm really glad the update fixes this for you.

Pat




--
Pat McMillan
Macintosh Business Unit
Microsoft Corp.
This posting is provided ³AS IS² with no warranties, and confers no rights.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding the right style field in VBA 0
Another SUMIFS issue 0
¿How to divide a total into 0
Finding the proper style field and the numbered paragraph value within the style 0
Vlookup or similar 7
SUMIFS 4
Excel 08 - Splitting tables 4
Convertibility Issue between Windows Excel 2007 and Mac Excel 2008 5

Top