Excel 2008 : SUMIF using Name and Date Range criteria

**cloudydaysong** · 10-18-2010, 06:34 PM

Attempting to create a form sheet that compares the sums of several employees' sales in specific categories, between two different date ranges. When the 'Start Date' and 'End Date' on the top of either Range are adjusted, data in the categories should change.

The specific snag I am encountering, is finding a SUMIFS statement whose criteria are Name, and Date Range. I have considered using DSUM as an alternate function, but the layout of my 'Display' sheet prohibits it.

Specifics are as follows:

• The 'Tracker Entry Form' sheet houses all of the data entered from each employee's tracker. Name is Column A, Date is Column B, and the specific sales category I am tracking is in Column D.

• The 'Date-Rage Comparison' sheet is where my formulas should be, in cell C10. Name is Column A on this sheet as well. End Date is stored in cell G5, and Start Date in C5. The formula I am working on is regarding employee whose name is stored in A10.

The formula I am using, which returns a #VALUE! error, is:
=SUMIFS('Tracker Entry Form'!D:D,'Tracker Entry Form'!B:B,"<="&G5,'Tracker Entry Form'!A:A,A10)-SUMIFS('Tracker Entry Form'!D:D,'Tracker Entry Form'!B:B,"<"&C5,'Tracker Entry Form'!A:A,A10)

Any help on this is GREATLY appreciated, I am stuck and frustrated beyond belief. Thank you all!

**daddylonglegs** · 10-18-2010, 07:11 PM

That formula works for me, I imagine you have some problem with the data.......are there #VALUE errors anywhere in the data?

You could do everything in one SUMIFS, that'd be easier, i.e.

=SUMIFS('Tracker Entry Form'!D:D,'Tracker Entry Form'!B:B,"<="&G5,'Tracker Entry Form'!B:B,">="&C5,'Tracker Entry Form'!A:A,A10)

...but if you get an error with the previous one I imagine you'll get an error with that too......

**cloudydaysong** · 10-18-2010, 07:53 PM

daddylonglegs,

Thanks for your help! There are no #VALUE! errors in the data, only in other parts of the same form that have formulas including C10 (location of the formula in question). I have tried your formula and it returns the same #VALUE! error. Also tried removing all data from the Tracker Entry Form sheet and entering in a few to test, however this also does not remove the error.

Thank you again for your help! Any other suggestions or ideas are certainly appreciated.

**scottylad2** · 10-18-2010, 08:06 PM

Is your data definetley all numbers and not some text sneaking in there? Value errors occur most of the time when you try to add text to numbers

**cloudydaysong** · 10-18-2010, 08:22 PM

No text in any inappropriate fields. Only in the Name fields, thank you though.

I am starting to suspect that mixing Date data with name data is the culprit, does anyone have experience mixing these types of data in a SUMIFS formula?

**DonkeyOte** · 10-19-2010, 03:09 AM

Given the formula provided only an underlying #VALUE! error would cause a #VALUE! result - this includes criteria cells.

If the ranges used were not all of the same size that too would generate a #VALUE! error in SUMIFS but here you're using entire column references for each range so that should not be the case.

Originally Posted by scottylad2

Is your data definetley all numbers and not some text sneaking in there? Value errors occur most of the time when you try to add text to numbers

Originally Posted by cloudydaysong

I am starting to suspect that mixing Date data with name data is the culprit, does anyone have experience mixing these types of data in a SUMIFS formula?

If the data types were incompatible that would affect the result in terms of the aggregate result but it would not generate an error as no explicit coercion takes place in SUMIFS
(though you could argue over numeric string criteria being treated as numbers - as coercion of a sort)

**cloudydaysong** · 10-19-2010, 06:53 PM

Okay, today I took this project into work and opened it on a Windows PC, using Excel 2007, it worked fine. Any idea why this error would occur on Excel '08 for Mac?

Also, any other ideas for getting around the error while accomplishing the same goal?

As always, thank you so much for your help!

**DonkeyOte** · 10-19-2010, 07:10 PM

OK well in my mind I figured there's no reason why it shouldn't work in XL2008 so a quick google threw up:

http://www.officeformac.com/ms/ProductForums/Excel/2620

it seems you need to ensure you have installed the following update:

Originally Posted by Bob Greenblatt MVP, Macintosh

the fix is the Office 12.1.3 update available from Mactopia.

**cloudydaysong** · 10-19-2010, 09:06 PM

Such a simple fix for so many hours of troubleshooting! Ah, such is the nature of this work... Thank you so much for your help, I've installed the update and magic. Now on to unbridled productivity, cheers!