adding columns based on dates in another colu

September 30, 2010 at 10:50:50
Specs: Windows XP
I am not sure if what I want to do is possable or not. I have a spread sheet with three colums, date, total, daily total. First of all the dates in will very and have multiple entries for the same day or just one depending on service. I want to make one entry in the daily total column on the last entry of the day with the total for the day. So i need to check dates in column date and if the same add the corrosponding row in time for one entry. And I am working with Excel 2003. Is this possable? If so can someone please help me!!

See More: adding columns based on dates in another colu

Report •


#1
September 30, 2010 at 11:11:37
Try =SUMIF()

If your data looks like this:

      A           B
1) 9/30/2010     1.00
2) 10/1/2010     2.00
3) 10/1/2010     2.00
4) 10/3/2010     2.00
5) 10/4/2010     1.00
6) 10/1/2010     1.00
7) 10/6/2010     1.00
8) 10/7/2010     1.00
9) 10/8/2010     1.00

Then to find the total for the date 10/1/2010 use this formula:

=SUMIF(A1:A9,"10/1/2010",B1:B9)

You could also do it by selecting a date from your date column like this:

=SUMIF(A1:A9,A6,B1:B9)

MIKE

http://www.skeptic.com/


Report •

#2
September 30, 2010 at 11:55:35
The only problem is the dates will never be the same in the spreedsheet. And I am trying to get the totals on just one line. My spreedsheet look something like this and where the x's are is where I would like the totals but this is going to change all the time based on the client activities per month and day.

Date Total Time Daily Total
8/27/10 1
9/10/10 2
9/10/10 3 X
9/12/10 4
9/12/10 5
9/12/10 6 X
9/15/10 7
9/15/10 8
9/15/10 9
9/15/10 10 X


So is this possible?




Report •

#3
September 30, 2010 at 12:27:39
My spreadsheet look something like this

Please post what your spreadsheet really looks like.
Change any data that your not comfortable posting.
Use the PRE tags, located at the top of the reply window, they will keep things neat. Use spaces between columns.
Also use the Preview Follow Up at the bottom to take a look at your post before you actually post it.
You can then make corrections and by checking the box at the top you can preview it again.

This will not work?


        A          B          C  
 1) Date      Total Time   Daily Total	
 2) 8/27/2010     1
 3) 9/10/2010     2
 4) 9/10/2010     3          5	<< =SUMIF(A2:A11,A4,B2:B11)
 5) 9/12/2010     4
 6) 9/12/2010     5
 7) 9/12/2010     6         15	<< =SUMIF(A2:A11,A7,B2:B11)
 8) 9/15/2010     7
 9) 9/15/2010     8
10) 9/15/2010     9
11) 9/15/2010    10         34	<< =SUMIF(A2:A11,A11,B2:B11)

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
October 1, 2010 at 07:40:32
This did work thank you and I added in an if statement so the information will calculate allt he way down the spreedsheet but only appear on the last entry for the perticular date. Thank you for your help. So my ending formula looked like this (this id exact copy and my spreedsheet date data is from column A rows 9 on and values to sum are al in column B row 9 on)

=IF(A9=A10,,(SUMIF(A$9:A9,A9,B$9:B9)))


again thank you


Report •

#5
October 1, 2010 at 07:53:46
Another question how would I get the same totals added together for dates in a range that are entered in to different cells? This is what my spreedsheet looks like so far:
				
				
Date	Total Time	Daily Total						
8/27/10	 1.50	 1.50						
9/10/10	 2.75							
9/10/10	 3.00	 5.75						
9/12/10	 4.00							
9/12/10	 5.00							
9/12/10	 6.00	 15.00						
9/15/10	 7.00							
9/15/10	 8.00							
9/15/10	 9.00							
9/15/10	 10.00	 34.00						
9/29/10	 1.00	 1.00						
10/1/10	 13.00							
10/1/10	 2.00	 15.00						
10/6/10	 3.00							
10/6/10	 10.00	 13.00						
10/14/10	 4.00	 4.00		CURRENT AUTHORIZATION				
10/24/10	 6.00	 6.00			9/1/10	TO	9/25/10	
				APS ID#: 			UNITS	500
						UNUSED UNITS		 ?????
				CSR FOR ADDITIONAL UNITS				
						TO		
				APS ID#: 			UNITS	
						UNUSED UNITS		
				NEW CSR				
						TO		
				APS ID#: 			UNITS	
						UNUSED UNITS		
								
				# Units Remaining				


Not Sure how to get me column and rows markers to show up.
But I want to try and get a total where the ???? are that has the total daily hours for the dates with in the range of the dates in the cells above the ????. Does anyone have any ideas? Any help would be greatly appreciated. thank you


Report •

#6
October 1, 2010 at 07:57:18
Sorry just noticed that my last two days the data is out of line. And the daily totals are not completely under the right column. I am sorry I am new to posting questions on here please bear with me as I learn thank you

Report •

#7
October 1, 2010 at 10:28:24
You could use a =SUMPRODUCT() function.

Try this:

In the cell with the ??? (I22 on my sheet) enter the formula:

=SUMPRODUCT(--($A$4:$A$20>=$F$20),--($A$4:$A$20<=$H$20),$C$4:$C$20)

It will use the two dates give in the Current Authorization
section as the start & end dates.

           E                    F             G            H         I
19) CURRENT AUTHORIZATION   |           |             |           |
20)                         | 9/1/2010  |TO           | 9/25/2010 |
21) APS ID#:                |           |UNITS        |           |  500
22)                         |           |UNUSED UNITS |  Formula>>|54.75
23) CSR FOR ADDITIONAL UNITS|           |             |           |

MIKE

http://www.skeptic.com/


Report •

#8
October 1, 2010 at 11:23:29
re: "Sorry just noticed that my last two days the data is out of line."

To get your data to line up correctly when you post at this site (ike Mike's does) follow Mike's advice from another thread:

Tip:
If you use the pre tags found above the comments box, you can line up your example data to make it easier for us to read.
Also you can preview your reply by clicking “Preview Follow Up” before actually posting and straighten out anything that doesn’t look right.
And you can repeat the process by checking the box next to "Check To Show Confirmation Page Again


Report •

#9
October 1, 2010 at 11:47:03
That worked thank you. I have another twist how would I make it so that cell only calculated if the dates in column A exceeded the end date in the cell above the question marks? I am not sure how to write an if statement based on using a range if its even possible.

Report •

#10
October 1, 2010 at 13:05:13
if the dates in column A exceeded the end date in the cell above the question marks?
Exceeded meaning greater than?

=SUMPRODUCT(--($A$2:$A$48>$H$20),$C$2:$C$48)

MIKE

http://www.skeptic.com/


Report •

#11
October 1, 2010 at 13:51:57
OR, you could use another =SUMIF() function like:

=SUMIF($A$2:$A$48,">"&$G$18,$C$2:$C$48)

MIKE

http://www.skeptic.com/


Report •


Ask Question