# 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

#1
October 22, 2009 at 09:58:09
 Try using a =SUMIF() functionPut the date you want to total in Cell A7In 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 6) 7) 10/22/2009 10 ```MIKEhttp://www.skeptic.com/

Report •

#2
October 22, 2009 at 09:58:14
 Hi,The simple answer is to use the SUMIF() functionIf your dates are in the range A2 to A21and the values are in B2 to B21Then to get the sum of all values for say 04-Aug-2009enter this formula in any empty cell:=SUMIF(\$A\$2:\$A\$21,"04/08/2009",\$B\$2:\$B\$21)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=SUMIF(\$A\$2:\$A\$21,C2,\$B\$2:\$B\$21)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-09and "dd-mmmm-yyyy" will show 04-August-2009Your 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.Regards

Report •

#3
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

#4
October 22, 2009 at 11:18:53
and
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.Regards

Report •

#5
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 •

#6
October 22, 2009 at 12:22:11
 Hi,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 ```Regards

Report •

#7
October 22, 2009 at 12:38:22
 Hi,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()-31in C3 enter =C2+1Drag the formula in C2 down 30 rows.Format all of these cells with your preferred date format.In D2 enter this formula:=IF(ISERROR(VLOOKUP(C2,\$A\$2:\$A\$28,1,FALSE)),"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 datesThis will give the daily totals for all entries in columns A & B that fall within the last month (approx.).Regards

Report •

#8
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 •

#9
October 22, 2009 at 13:57:11
 Hi,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 A2. From the Ribbon select Data - Sort & Filter - Advanced3. You may be 'asked' whether to extend the selection - click No3. 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.Regards

Report •

#10
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 fileDelete unnecessary columnssort datesextract unique datesadd repeated datesThat will have to be a "tune in next time" though. :)Thanks again for all the help guys!

Report •