including a date range in SUMIF

**Emma Blower** · 01-08-2014, 10:48 AM

Okay so i don't really know how to explain this but here goes . . .

We use a spreadsheet to keep track of time spent working for over 100 different companies. It's not very complex but it contains a lot of 'job entries' for record-keeping purposes; as well as a separate section totaling the hours spent on each company - this is the bit i need help with.

Each company has a unique code which identifies that the recorded hours are allocatable to them. In the totaling section I've used the SUMIF formula to pull up the recorded hours and show totals for each company; this works, but pulls up EVERY hour on the sheet recorded for that company.

I want to show a month-by-month total for each company, which resets back to 0 every month. I know i could use the filter tool to separate job entries into months, and then change the cell range in my formula to pull out hours recorded within these entries only ... but hopefully there is a quicker/easier way.

I want a formula which will pick up the company code and date work was done, and then add the hours to the company's total for that month. I don't want the total hours to accumulate, i want each month to start fresh.

If anyone can advise how to add a date range to the formula (in layman's terms please) i would be most grateful.

Thanks in advance and apologies for the state of my question - i don't really know what i'm talking about

**Tony Valko** · 01-08-2014, 11:05 AM

Try something like this...

Data Range

	A	B	C	D	E	F	G	H
1	Company	Date	Hours	-----	Company	1/1/2013	2/1/2013	3/1/2013
2	4	1/1/2013	73		1	64	29	3
3	5	1/22/2013	4		2	0	85	0
4	4	2/8/2013	19		3	40	0	0
5	1	1/25/2013	64		4	73	19	34
6	2	2/20/2013	85		5	4	0	0
7	1	3/15/2013	3
8	4	3/12/2013	34
9	3	1/7/2013	40
10	1	2/25/2013	29

This formula entered in F2:

=SUMIFS($C$2:$C$10,$A$2:$A$10,$E2,$B$2:$B$10,">="&F$1,$B$2:$B$10,"<="&EOMONTH(F$1,0))

Copy across as needed then down as needed.

The dates in F1:H1 are the 1st of the month dates. You can format them to display just the month name if you'd like.

**Emma Blower** · 01-09-2014, 08:04 AM

Thank you for your reply, it worked perfectly

Emma

**Tony Valko** · 01-09-2014, 05:24 PM

You're welcome. Thanks for the feedback!

If your question has been answered please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools>Mark this thread as solved.