Conditional summing from table

[doonami]

Hi, I'm currently trying to create a sum of event times from a table with a variable number of entries per day. eg.

[pre]

Day	        Events          	Minutes
1/05/2012	dog ran away	        10
1/05/2012	explosion	        20
2/05/2012	production halt	        45
3/05/2012	grocery shortage	15
4/05/2012	someone forgot	        60
4/05/2012	fault	                120
4/05/2012	lost dog	        100

[/pre]

What could I do to create a 'daily total minutes' figure for each day from this list?


I have tried using IF, or SUMIF, but I can't think of a way to do this without knowing exactly how many entries there will be for each given day.


Thanks for your help!

 

[Hui]

Doonami


Firstly, Welcome to the Chandoo.org forums.


You can use a number of techniques including Sumproduct and Sumif


I have assumed that C1 has the date eg: 1/5/2012


=SUMPRODUCT((A2:A100=E1)*(C2:C100))

or

=SUMIF(A2:A100,E1,C2:C100)

 

[doonami]

Thank you Hui, that's almost perfect for what I need. I'm trying to make my sheet as dummy proof as possible, so people can populate that table and the spreadsheet will spit out a daily average.


Is there a way that I can make this dynamically update? I've considered using a separate table (Table B) to house unique dates derived from the original table (Table A) then use the SUMIF formula to reference Table B's date, instead of on the original Table A (in which there is no definite distance between unique dates).


I can currently do this with the Filter advanced options and copying unique entries, but this seems to be a one-off copy and doesn't update with an expanding table.


Any ideas on this front are also appreciated

 

[Hui]

Doonami


This is an ideal candidate for using Named Formula


So instead of using

=SUMIF(A2:A100,E1,C2:C100)  or [code]=SUMPRODUCT((A2:A100=E1)*(C2:C100))

You would establish 2 Named Formula

Day:  [code]=OFFSET(Sheet1!A2,,,COUNTA(Sheet1!$A:$A)-1)

Minutes: =OFFSET(Day,,2)

Then use the Named Formula in the above formulas:

=SUMIF(Day,E1,Minutes)[/code]

or

=SUMPRODUCT((Day=E1)*Minutes)[/code]