Bifurcate January 2020 and January 2021

[Muthukrishnan V]

Excel 365

I have a worksheet 0f daily expenses.

Column A: dates in dd-mm-yyyy format. Commencing A4 as 01-01-2020, A5 as 02-01-2020 and so on
upto A 369 as 31-12-2020.

Column B4 to B369 expenses amount in numerical (whole numbers like 25263 without digits)

Column C4 to Column C369 contains following formula to give MONTHLY cumulative expenses.
Formula in Column C4 copied upto C369.
Column C4 formula:
=IF(A4>TODAY," ",SUMPRODUCT(--(MONTH($A$4:A4)=MONTH(A4)),($B$4:b4)))
I am comfortable from 01-01-2020 to 31-12-2020.

On 01-01-2021 (with the start of fresh year), in A370 I typed 01-01-2021; in B370 I typed 14800 and
then copied formula in C 369 to C370.
Now Cell C370 shows 93100 (being month of January 2020 total expenses 78300 plus 01-01-2021 expenses 14800)

I request you to kindly advise me formula in Column C to start afresh monthwise totals for the year 2021

 

[alansidman]

How to Sum Values Based on Month and Year in Excel - Free Excel Tutorial

This post will guide you How to Sum Values Based on Month and Year in Excel.

www.excelhow.net

 

[Muthukrishnan V]

=IF(A4>TODAY," ",SUMPRODUCT(--(MONTH($A$4:A4)=MONTH(A4)),($B$4:b4)))

What should be the revised formula please?

 

[Peter_SSs]

Try

=IF(A4>TODAY(),"",SUMIF(A$4:A4,">"&A4-DAY(A4),B$4:B4))

 

[Dave Patton]

Each of the following should work; select your preference.
The sumifs is probably the fastest.

T202101a.xlsm
	A	B	C	D	E
4	1-Jan-20	25263	25263	25263	25263
4c

Cell Formulas
Range	Formula
C4	C4	=IF(A4>TODAY(),"",SUMPRODUCT(--(TEXT($A$4:A4,"yymm")=TEXT(A4,"yymm")),$B$4:B4))
D4	D4	=IF(A4>TODAY(),"",SUMIF(A$4:A4,">"&A4-DAY(A4),B$4:B4))
E4	E4	=IF(A4>TODAY(),"",SUMIFS(B$4:B4,A$4:A4,">"&A4-DAY(A4)))

 

[Muthukrishnan V]

Thank you Sirs, Works fine.

 

[Peter_SSs]

Thank you Sirs, Works fine.

Glad you got a satisfactory result from the various suggestions. Thanks for letting us know.