Thread: SUMIF using Name and Date Range criteria

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!

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......

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)

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.