SOLVED

Excel - match dates in dfferent formats

Brass Contributor

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

13 Replies

@JennySommet 

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)

@Sergei Baklan 

 

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!

Hi,

Try below formula:

=SUMPRODUCT((YEAR(Sheet2!$B$2:$B$100000)=YEAR($A2))*(MONTH(Sheet2!$B$2:$B$100000)=MONTH($A2))*(Sheet2!$C$2:$C$100000=B$1)*(Sheet2!$D$2:$D$100000))

Hope this helps.

Regards,
IlirU
best response confirmed by JennySommet (Brass Contributor)
Solution

@JennySommet 

Sorry 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)
Thank you! This is so useful for me and all my other spreadsheets!
Thank you! this works!

@JennySommet , you are welcome, glad it helped

Hello!
On the same subject how do I get the formula below:

=SUMIFS(Sheet2!$D:$D, Sheet2!$B:$B,">" & EOMONTH($A26,-1), Sheet2!$B:$B,"<=" & EOMONTH($A26,0), Sheet2!$C:$C,E$1)

To reflect <= Dec 19 rather the formula above which would bring back number of records just for month of Dec 19.

Thank you!

@JennySommet 

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)
Thank you! Is this the same as the formula below if cell A6 = Dec 19 then:

$B:$B,"<=" & EOMONTH($A6,0) would mean less than or equal to Dec 19?

I just want a formula for any month I put in the cell, it doesn't have to be Dec 19. Thank you!

@JennySommet 

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

 

Thank you, so useful!!

@JennySommet , glad to help

1 best response

Accepted Solutions
best response confirmed by JennySommet (Brass Contributor)
Solution

@JennySommet 

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

View solution in original post