Running Totals for Dates

[Guest]

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

 

[Guest]

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

 

[Guest]

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")

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

Sorry but I don't quire understand your requirement. Can you post a sample
w/book to me (at (e-mail address removed))

Remove nospam.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

Do you want by Date only or Date AND Currency?

Take a look at Pivot Tables which might meet you need.

 

[Guest]

just totaled by each dat
--
thank you
censura

Do you want by Date only or Date AND Currency?

Take a look at Pivot Tables which might meet you need.

 

[Guest]

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