Mar 10 2021 01:42 AM
Hello,
In the excel attached I am trying to sum all the dates in the second tab into a different format on the first tab.
i.e Sum of all dates in January 2018. Even when I format the dates in second tab to same format as first tab, the formula does not work.
Really appreciate support on this
Many thanks
Jenny
Mar 10 2021 02:42 AM
Format doesn't matter. To sum within month you may use
=SUMIFS(Sheet2!$D:$D,Sheet2!$C:$C,">" & EOMONTH(B$1,-1),Sheet2!$C:$C,"<=" & EOMONTH(B$1,0),Sheet2!$B:$B,$A2)
Mar 10 2021 06:53 AM
Sorry please see attached. The highlighted cell should bring back 423. The school term is not a date. I am trying to match column B on sheet 2, with column A on sheet 1
Thank you!
Mar 10 2021 08:20 AM
Mar 10 2021 02:25 PM
SolutionSorry for the misprint. Corrected formula is
=SUMIFS(Sheet2!$D:$D, Sheet2!$B:$B,">" & EOMONTH($A26,-1), Sheet2!$B:$B,"<=" & EOMONTH($A26,0), Sheet2!$C:$C,E$1)
Mar 11 2021 12:19 AM
Mar 11 2021 12:28 AM
@JennySommet , you are welcome, glad it helped
Mar 18 2021 11:28 AM
Mar 18 2021 12:47 PM
That could be
=SUMIFS(Sheet2!$D:$D, Sheet2!$B:$B,">=" & DATE(2019,12,1), Sheet2!$B:$B,"<=" & DATE(2019,12,31), Sheet2!$C:$C,E$1)
Mar 18 2021 02:19 PM
Mar 19 2021 03:25 AM
Yes, practically the same. If in A6 is Dec 15, 2019 when
EOMONTH(A6,-1) returns Nov 30, 2019
EOMONTH(A6,0) returns Dec 31, 2019
Mar 10 2021 02:25 PM
SolutionSorry for the misprint. Corrected formula is
=SUMIFS(Sheet2!$D:$D, Sheet2!$B:$B,">" & EOMONTH($A26,-1), Sheet2!$B:$B,"<=" & EOMONTH($A26,0), Sheet2!$C:$C,E$1)