| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Running Totals for Dates
I am making a spread sheet that lists meeting times. One line will show the
specific date the next column shows the length of the meeting. What I would like to do is to have a way to have a formula that will add the times for the months together. Example: Jan-12 75mins Jan-22 25mins Feb-1 10mins Mar-16 15mins Feb-5 25mins I want to have it so in another section it will display these results Jan - 100mins Feb - 15mins Mar - 15mins |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running Totals for Dates
With dates in column A and times in B:
=SUMPRODUCT((MONTH($A$1:$A$5)=1)*($B$1:$B$5)) will give total for January ... and this for February =SUMPRODUCT((MONTH($A$1:$A$5)=2)*($B$1:$B$5)) HTH "Chart_Maker_Wonderer" wrote: I am making a spread sheet that lists meeting times. One line will show the specific date the next column shows the length of the meeting. What I would like to do is to have a way to have a formula that will add the times for the months together. Example: Jan-12 75mins Jan-22 25mins Feb-1 10mins Mar-16 15mins Feb-5 25mins I want to have it so in another section it will display these results Jan - 100mins Feb - 15mins Mar - 15mins |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running Totals for Dates
I assume your data has two columns. the first column is a time in the format
month and day. The 2nd column is a number. Then the answer is simple. If the range of the data is A1:B100. The the summary will be Add a new column to your table that have the month number in column C In Cell C1 =month(a1) the your summary will be ="Jan - " & TEXT(SUMIF(C$1:C$100,"=1",B$1:B$100),"general") ="Feb - " & TEXT(SUMIF(C$1:C$100,"=2",B$1:B$100),"general") ="Mar - " & TEXT(SUMIF(C$1:C$100,"=3",B$1:B$100),"general") .... ="Dec - " & TEXT(SUMIF(C$1:C$100,"=12",B$1:B$100),"general") "Chart_Maker_Wonderer" wrote: I am making a spread sheet that lists meeting times. One line will show the specific date the next column shows the length of the meeting. What I would like to do is to have a way to have a formula that will add the times for the months together. Example: Jan-12 75mins Jan-22 25mins Feb-1 10mins Mar-16 15mins Feb-5 25mins I want to have it so in another section it will display these results Jan - 100mins Feb - 15mins Mar - 15mins |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running Totals for Dates
If your B column is really 75mins as opposed to just the number 75 then you
will have a problem evaluating that and will need to extract the numeric part before you can add it up. =LOOKUP(9.99999999999999E+307,--LEFT(B1,ROW(INDIRECT("1:"&LEN(B1))))) insterted in column C and dragged down will extract the numeric part from column B. Then =SUMPRODUCT((MONTH($A$1:$A$100)=1)*($C$1:$C$100)) will do the summing required. This formula will check for January so substitute 2 for February etc. Mike "Chart_Maker_Wonderer" wrote: I am making a spread sheet that lists meeting times. One line will show the specific date the next column shows the length of the meeting. What I would like to do is to have a way to have a formula that will add the times for the months together. Example: Jan-12 75mins Jan-22 25mins Feb-1 10mins Mar-16 15mins Feb-5 25mins I want to have it so in another section it will display these results Jan - 100mins Feb - 15mins Mar - 15mins |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running Totals for Dates
That worked great.
If there a way that I can have it so it will make a result based on this: I am currently working on a chart that will calculate meeting times, I have a chart setup at the bottom of the page where it shows each month. If the month of the meeting is janruary then it will take the time and add it to the janruary column, if the person was at that meeting (I have a coloumn setup for each person where something is typed into the column if the person was not there, otherwise it is blank to say that the person was there. "Toppers" wrote: With dates in column A and times in B: =SUMPRODUCT((MONTH($A$1:$A$5)=1)*($B$1:$B$5)) will give total for January ... and this for February =SUMPRODUCT((MONTH($A$1:$A$5)=2)*($B$1:$B$5)) HTH "Chart_Maker_Wonderer" wrote: I am making a spread sheet that lists meeting times. One line will show the specific date the next column shows the length of the meeting. What I would like to do is to have a way to have a formula that will add the times for the months together. Example: Jan-12 75mins Jan-22 25mins Feb-1 10mins Mar-16 15mins Feb-5 25mins I want to have it so in another section it will display these results Jan - 100mins Feb - 15mins Mar - 15mins |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running Totals for Dates
|
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running Totals for Dates
It is just a number not a minute. I just put the 'mins' there to help
illustrate what i was asking. I got some help from Toppers with this formula. "Mike" wrote: If your B column is really 75mins as opposed to just the number 75 then you will have a problem evaluating that and will need to extract the numeric part before you can add it up. =LOOKUP(9.99999999999999E+307,--LEFT(B1,ROW(INDIRECT("1:"&LEN(B1))))) insterted in column C and dragged down will extract the numeric part from column B. Then =SUMPRODUCT((MONTH($A$1:$A$100)=1)*($C$1:$C$100)) will do the summing required. This formula will check for January so substitute 2 for February etc. Mike "Chart_Maker_Wonderer" wrote: I am making a spread sheet that lists meeting times. One line will show the specific date the next column shows the length of the meeting. What I would like to do is to have a way to have a formula that will add the times for the months together. Example: Jan-12 75mins Jan-22 25mins Feb-1 10mins Mar-16 15mins Feb-5 25mins I want to have it so in another section it will display these results Jan - 100mins Feb - 15mins Mar - 15mins |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running Totals for Dates
Toppers hope you can help it is a similar to the previous thread you helped
with. I have two columns a and f a has dates in it ie 02-jan , 02-jan, 03-jan with trades taken that day across different currencies thus 02-jan -20 gbp 02-Jan +45 chf 02-Jan -11 eur 03-jan +34 gbp What i would like to do is habe totals for each day and then this data imported into a equity graph. I would also like the graph to be updated as new entries are made each day and added to the table. hope you can help thank you -- thank you censura "Toppers" wrote: With dates in column A and times in B: =SUMPRODUCT((MONTH($A$1:$A$5)=1)*($B$1:$B$5)) will give total for January ... and this for February =SUMPRODUCT((MONTH($A$1:$A$5)=2)*($B$1:$B$5)) HTH "Chart_Maker_Wonderer" wrote: I am making a spread sheet that lists meeting times. One line will show the specific date the next column shows the length of the meeting. What I would like to do is to have a way to have a formula that will add the times for the months together. Example: Jan-12 75mins Jan-22 25mins Feb-1 10mins Mar-16 15mins Feb-5 25mins I want to have it so in another section it will display these results Jan - 100mins Feb - 15mins Mar - 15mins |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running Totals for Dates
Do you want by Date only or Date AND Currency?
Take a look at Pivot Tables which might meet you need. "censura" wrote: Toppers hope you can help it is a similar to the previous thread you helped with. I have two columns a and f a has dates in it ie 02-jan , 02-jan, 03-jan with trades taken that day across different currencies thus 02-jan -20 gbp 02-Jan +45 chf 02-Jan -11 eur 03-jan +34 gbp What i would like to do is habe totals for each day and then this data imported into a equity graph. I would also like the graph to be updated as new entries are made each day and added to the table. hope you can help thank you -- thank you censura "Toppers" wrote: With dates in column A and times in B: =SUMPRODUCT((MONTH($A$1:$A$5)=1)*($B$1:$B$5)) will give total for January ... and this for February =SUMPRODUCT((MONTH($A$1:$A$5)=2)*($B$1:$B$5)) HTH "Chart_Maker_Wonderer" wrote: I am making a spread sheet that lists meeting times. One line will show the specific date the next column shows the length of the meeting. What I would like to do is to have a way to have a formula that will add the times for the months together. Example: Jan-12 75mins Jan-22 25mins Feb-1 10mins Mar-16 15mins Feb-5 25mins I want to have it so in another section it will display these results Jan - 100mins Feb - 15mins Mar - 15mins |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running Totals for Dates
just totaled by each dat
-- thank you censura "Toppers" wrote: Do you want by Date only or Date AND Currency? Take a look at Pivot Tables which might meet you need. "censura" wrote: Toppers hope you can help it is a similar to the previous thread you helped with. I have two columns a and f a has dates in it ie 02-jan , 02-jan, 03-jan with trades taken that day across different currencies thus 02-jan -20 gbp 02-Jan +45 chf 02-Jan -11 eur 03-jan +34 gbp What i would like to do is habe totals for each day and then this data imported into a equity graph. I would also like the graph to be updated as new entries are made each day and added to the table. hope you can help thank you -- thank you censura "Toppers" wrote: With dates in column A and times in B: =SUMPRODUCT((MONTH($A$1:$A$5)=1)*($B$1:$B$5)) will give total for January ... and this for February =SUMPRODUCT((MONTH($A$1:$A$5)=2)*($B$1:$B$5)) HTH "Chart_Maker_Wonderer" wrote: I am making a spread sheet that lists meeting times. One line will show the specific date the next column shows the length of the meeting. What I would like to do is to have a way to have a formula that will add the times for the months together. Example: Jan-12 75mins Jan-22 25mins Feb-1 10mins Mar-16 15mins Feb-5 25mins I want to have it so in another section it will display these results Jan - 100mins Feb - 15mins Mar - 15mins |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Running Totals for Dates
Assume column X has a list of dates (1-Jan, 2-Jan etc) starting row 2
Put this formula in Y2 and copy down for all dates in column X =SUMPRODUCT(--($A$2:$A$100=X2),($F$2:$F$100)) Change range of A and F to suit but they cannot be a total column (A:A is not valid) HTH "censura" wrote: just totaled by each dat -- thank you censura "Toppers" wrote: Do you want by Date only or Date AND Currency? Take a look at Pivot Tables which might meet you need. "censura" wrote: Toppers hope you can help it is a similar to the previous thread you helped with. I have two columns a and f a has dates in it ie 02-jan , 02-jan, 03-jan with trades taken that day across different currencies thus 02-jan -20 gbp 02-Jan +45 chf 02-Jan -11 eur 03-jan +34 gbp What i would like to do is habe totals for each day and then this data imported into a equity graph. I would also like the graph to be updated as new entries are made each day and added to the table. hope you can help thank you -- thank you censura "Toppers" wrote: With dates in column A and times in B: =SUMPRODUCT((MONTH($A$1:$A$5)=1)*($B$1:$B$5)) will give total for January ... and this for February =SUMPRODUCT((MONTH($A$1:$A$5)=2)*($B$1:$B$5)) HTH "Chart_Maker_Wonderer" wrote: I am making a spread sheet that lists meeting times. One line will show the specific date the next column shows the length of the meeting. What I would like to do is to have a way to have a formula that will add the times for the months together. Example: Jan-12 75mins Jan-22 25mins Feb-1 10mins Mar-16 15mins Feb-5 25mins I want to have it so in another section it will display these results Jan - 100mins Feb - 15mins Mar - 15mins |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Running totals | Excel Discussion (Misc queries) | |||
| Running Totals | Excel Discussion (Misc queries) | |||
| Running Totals | Excel Worksheet Functions | |||
| Running Totals | New Users to Excel | |||
| running totals | Setting up and Configuration of Excel | |||
Linear Mode
