Bifurcate January 2020 and January 2021

Muthukrishnan V

Board Regular
Joined
May 29, 2008
Messages
185
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Muthukrishnan V

Board Regular
Joined
May 29, 2008
Messages
185
Office Version
  1. 365
Platform
  1. Windows
=IF(A4>TODAY," ",SUMPRODUCT(--(MONTH($A$4:A4)=MONTH(A4)),($B$4:b4)))

What should be the revised formula please?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Try

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

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,629
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

T202101a.xlsm
ABCDE
41-Jan-2025263252632526325263
4c
Cell Formulas
RangeFormula
C4C4=IF(A4>TODAY(),"",SUMPRODUCT(--(TEXT($A$4:A4,"yymm")=TEXT(A4,"yymm")),$B$4:B4))
D4D4=IF(A4>TODAY(),"",SUMIF(A$4:A4,">"&A4-DAY(A4),B$4:B4))
E4E4=IF(A4>TODAY(),"",SUMIFS(B$4:B4,A$4:A4,">"&A4-DAY(A4)))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,612
Messages
5,625,839
Members
416,138
Latest member
Pizzaman22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top