Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

How to combine values

Hello


This time I have a hard time to reach the following.


In a table I have date values as well as duration values.

The target is now to have a cell programmed that the cell display the summ of all concerned cell having the same month or year.


Example Data


DateDuration
2015.1.11:30
2015.1.52:40
2015.2.27:20


Example Result

MonthSumm
January4:10
February7:20


Does anybody has a brilliant idea how to solve this problem.


Thank you so much in advance for any comments or solution.


Have a great day


Thomas

Posted on May 3, 2015 12:53 PM

Reply
Question marked as Best reply

Posted on May 3, 2015 3:42 PM

Hello


You may try something like this.


User uploaded file




Sum A1 2015 A2 1 A3 2 A4 3 A5 4 A6 5 A7 6 A8 7 A9 8 A10 9 A11 10 A12 11 A13 12 B1 month B2 =MONTHNAME(A2) B3 =MONTHNAME(A3) B4 =MONTHNAME(A4) B5 =MONTHNAME(A5) B6 =MONTHNAME(A6) B7 =MONTHNAME(A7) B8 =MONTHNAME(A8) B9 =MONTHNAME(A9) B10 =MONTHNAME(A10) B11 =MONTHNAME(A11) B12 =MONTHNAME(A12) B13 =MONTHNAME(A13) C1 sum C2 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A2,1),Data::A,"<"&EOMONTH(DATE($A$1,A2,1),0)+1) C3 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A3,1),Data::A,"<"&EOMONTH(DATE($A$1,A3,1),0)+1) C4 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A4,1),Data::A,"<"&EOMONTH(DATE($A$1,A4,1),0)+1) C5 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A5,1),Data::A,"<"&EOMONTH(DATE($A$1,A5,1),0)+1) C6 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A6,1),Data::A,"<"&EOMONTH(DATE($A$1,A6,1),0)+1) C7 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A7,1),Data::A,"<"&EOMONTH(DATE($A$1,A7,1),0)+1) C8 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A8,1),Data::A,"<"&EOMONTH(DATE($A$1,A8,1),0)+1) C9 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A9,1),Data::A,"<"&EOMONTH(DATE($A$1,A9,1),0)+1) C10 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A10,1),Data::A,"<"&EOMONTH(DATE($A$1,A10,1),0)+1) C11 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A11,1),Data::A,"<"&EOMONTH(DATE($A$1,A11,1),0)+1) C12 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A12,1),Data::A,"<"&EOMONTH(DATE($A$1,A12,1),0)+1) C13 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A13,1),Data::A,"<"&EOMONTH(DATE($A$1,A13,1),0)+1)




Notes.


Formulae in B2 and C2 can be filled down.


Target year is specified in A1.


You may hide column A if you want to.


Tables are built with Numbers v2.



Hope this may help,

H

3 replies
Question marked as Best reply

May 3, 2015 3:42 PM in response to tom61

Hello


You may try something like this.


User uploaded file




Sum A1 2015 A2 1 A3 2 A4 3 A5 4 A6 5 A7 6 A8 7 A9 8 A10 9 A11 10 A12 11 A13 12 B1 month B2 =MONTHNAME(A2) B3 =MONTHNAME(A3) B4 =MONTHNAME(A4) B5 =MONTHNAME(A5) B6 =MONTHNAME(A6) B7 =MONTHNAME(A7) B8 =MONTHNAME(A8) B9 =MONTHNAME(A9) B10 =MONTHNAME(A10) B11 =MONTHNAME(A11) B12 =MONTHNAME(A12) B13 =MONTHNAME(A13) C1 sum C2 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A2,1),Data::A,"<"&EOMONTH(DATE($A$1,A2,1),0)+1) C3 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A3,1),Data::A,"<"&EOMONTH(DATE($A$1,A3,1),0)+1) C4 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A4,1),Data::A,"<"&EOMONTH(DATE($A$1,A4,1),0)+1) C5 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A5,1),Data::A,"<"&EOMONTH(DATE($A$1,A5,1),0)+1) C6 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A6,1),Data::A,"<"&EOMONTH(DATE($A$1,A6,1),0)+1) C7 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A7,1),Data::A,"<"&EOMONTH(DATE($A$1,A7,1),0)+1) C8 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A8,1),Data::A,"<"&EOMONTH(DATE($A$1,A8,1),0)+1) C9 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A9,1),Data::A,"<"&EOMONTH(DATE($A$1,A9,1),0)+1) C10 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A10,1),Data::A,"<"&EOMONTH(DATE($A$1,A10,1),0)+1) C11 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A11,1),Data::A,"<"&EOMONTH(DATE($A$1,A11,1),0)+1) C12 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A12,1),Data::A,"<"&EOMONTH(DATE($A$1,A12,1),0)+1) C13 =SUMIFS(Data::B,Data::A,">="&DATE($A$1,A13,1),Data::A,"<"&EOMONTH(DATE($A$1,A13,1),0)+1)




Notes.


Formulae in B2 and C2 can be filled down.


Target year is specified in A1.


You may hide column A if you want to.


Tables are built with Numbers v2.



Hope this may help,

H

How to combine values

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.