Adding Values with Same Date

Microsoft Excel 2007
October 22, 2009 at 08:54:26
Specs: Windows XP
I have a simple 2 column spread sheet. Column A is dates and column B is values. If there are multiple rows with the same date then I want to add them together. So, if A3, A4 & A5 all have the same date, I want to add B3, B4 & B5. The sum can be put in Column C.

See More: Adding Values with Same Date

Report •

October 22, 2009 at 09:58:09
Try using a =SUMIF() function
Put the date you want to total in Cell A7
In Cell B7 put the formula =SUMIF(A1:A5,A7,B1:B5)

     A        B
1) 10/22/2009	1
2) 10/20/2009	2
3) 10/21/2009	3
4) 10/22/2009	4
5) 10/22/2009	5
7) 10/22/2009	10  


Report •

October 22, 2009 at 09:58:14

The simple answer is to use the SUMIF() function

If your dates are in the range A2 to A21
and the values are in B2 to B21

Then to get the sum of all values for say 04-Aug-2009
enter this formula in any empty cell:

The date to lookup can be in a different cell, rather than hardcoded into the formula.

If the date is in Cell C2, this formula in D2 will work
Note the $ signs. These are required if you are going to extend or drag the formula, as discussed below.

Using this approach you can put a series of dates in column C and then drag the formula in D2 down. It will use the series of dates and return the relevant totals.

	A		B	C		D
1	Dates		Values	Date to sum	Sum
2	4-Aug-09	10	1-Aug-09	0
3	4-Aug-09	11	2-Aug-09	41
4	4-Aug-09	13	3-Aug-09	0
5	6-Aug-09	13	4-Aug-09	65
6	11-Aug-09	12	5-Aug-09	0
7	4-Aug-09	14	6-Aug-09	13
8	4-Aug-09	17	7-Aug-09	0
9	29-Jul-09	19		
10	29-Jul-09	20		
11	2-Aug-09	17		
12	2-Aug-09	10		
13	2-Aug-09	14		
14	31-Jul-09	16		
15	10-Aug-09	19		
16	14-Aug-09	10		
17	11-Aug-09	20		
18	26-Jul-09	10		
19	10-Aug-09	10		
20	30-Jul-09	11		
21	27-Jul-09	15		

Note that all dates should be in a format that Excel recognizes. If you can change the way that a date is displayed by changing the date format, then Excel has recognized the entry in the cell as a date.

Format C2 as "dd-mm-yy" will show 04-08-09
and "dd-mmmm-yyyy" will show 04-August-2009

Your next issue will be how to keep this working as dates in column A change.

How do these dates get entered, and are they always within a range, such as a single month or the last 30 days.


Report •

October 22, 2009 at 11:06:33
I have an example in Excel but when I copy and paste the formatting is all screwed up. How do I post an example?

Report •

Related Solutions

October 22, 2009 at 11:18:53

Put your sample between <PRE> and </PRE> tags.

Either type them in or use the pre symbol above the reply box.

You can use the Preview option to see how it has worked and adjust as necessary. To add Tab characters in the reply box to further adjust spacing, you can copy an existing Tab space and copy & paste it, as hitting Tab doesn't work.


Report •

October 22, 2009 at 11:46:59
OK. Below is my example. I guess I should have posted this first so it was easier to understand my intentions. I believe what I am wanting to do is more complicated than the suggestions that have been posted which lead me to believe I haven't stated my problem very well. I will never be wanting to sum a specific date but rather the sum of all a date if there are any duplicates. Column A & B are what I am working with. Column C & D show my desired results. The date range will vary as time goes on but I'm guessing I will keep it about a month long.

A	    	 B		     C 	    	D
9/30/2009	1675.00		09/30/09	2445
9/30/2009	495.00		10/01/09	1345
9/30/2009	125.00		10/02/09	0
9/30/2009	150.00		10/05/09	0
10/1/2009	525.00		10/06/09	1329
10/1/2009	820.00		10/07/09	443
10/2/2009	0.00		10/08/09	2595
10/5/2009	0.00		10/09/09	490
10/6/2009	370.00		10/12/09	500
10/6/2009	703.00		10/13/09	443
10/6/2009	256.00		10/14/09	690
10/7/2009	443.00		10/15/09	97.5
10/8/2009	75.00		10/16/09	1287
10/8/2009	2520.00		10/19/09	748
10/9/2009	340.00		10/20/09	3740
10/9/2009	150.00		10/21/09	48
10/12/2009	500.00			
10/13/2009	443.00			
10/14/2009	690.00			
10/15/2009	97.50			
10/16/2009	1287.00			
10/19/2009	673.00			
10/19/2009	75.00			
10/20/2009	130.00			
10/20/2009	1805.00			
10/20/2009	1805.00			
10/21/2009	48.00			

Report •

October 22, 2009 at 12:22:11

Some confusion here !

The SUMIF() formula Mike and I suggested returns the values you have given.

I have run the SUMIF() formula using the data you provided and here are the results, alongside your results.

Date		Yours	Sumif()
30-Sep-09	2445.00	2445.00
1-Oct-09	1345.00	1345.00
2-Oct-09	0.00	0.00
5-Oct-09	0.00	0.00
6-Oct-09	1329.00	1329.00
7-Oct-09	443.00	443.00
8-Oct-09	2595.00	2595.00
9-Oct-09	490.00	490.00
12-Oct-09	500.00	500.00
13-Oct-09	443.00	443.00
14-Oct-09	690.00	690.00
15-Oct-09	97.50	97.50
16-Oct-09	1287.00	1287.00
19-Oct-09	748.00	748.00
20-Oct-09	3740.00	3740.00
21-Oct-09	48.00	48.00


Report •

October 22, 2009 at 12:38:22

If you want to make a rolling set of dates for the totals you could use the following:
If C2 is the first result date enter =TODAY()-31
in C3 enter =C2+1
Drag the formula in C2 down 30 rows.
Format all of these cells with your preferred date format.

In D2 enter this formula:
"No entry for this date",SUMIF($A$2:$A$28,C2,$B$2:$B$28))

I have split this onto two lines for ease of viewing, but it is all one formula. Note the $ signs.
Change the "No entry..." text as required.

Drag the formula down beside all your dates

This will give the daily totals for all entries in columns A & B that fall within the last month (approx.).


Report •

October 22, 2009 at 12:58:15
Ah. Sorry about that. I see what you are saying now. I wasn't following the part where these formulas require me to type in all the dates in a 3rd column. I see that your answer works great. Thanks for the help.
This leads me to another question. This list is going to get large and I don't want to have to type out the dates every time. The dates will already be in column A but there will be duplicates. How do I extract each date one time to create a column that only has the dates listed in column A? Basically this is going to be Monday through Friday ever week except for when there are holidays.
I tried typing in the dates for Monday through Friday for 2 weeks and then selecting the dates and dragging down but the auto-fill did not work correctly. This solution would not really work any ways because I wouldn't want any holidays that are during the week.
If there are holiday dates during the week, they will not be in column A so I'm wondering if there is a way to just extract each date one time.
I need this to be as less time consuming as possible as I will be having do this quite often.

Report •

October 22, 2009 at 13:57:11

You can do this manually, but reasonably quickly, and once you have followed this a couple of times it will be really easy!

1. Select all the dates in column A
2. From the Ribbon select Data - Sort & Filter - Advanced
3. You may be 'asked' whether to extend the selection - click No
3. In the box select Copy to another location and check 'Unique records only'
4. List range should already show the list of dates you selected. In the Copy to, select one cell at the top of Column C, where your results dates start e.g., C2.
5. Click OK, and you have a list of only the dates that occur in Column A.


Report •

October 22, 2009 at 14:07:34

Awesome. That works perfect. When I get the time, my next project will be to macro all this.
Open the file
Delete unnecessary columns
sort dates
extract unique dates
add repeated dates
That will have to be a "tune in next time" though. :)
Thanks again for all the help guys!

Report •

Ask Question