# Bifurcate January 2020 and January 2021

#### Muthukrishnan V

##### Board Regular
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

### 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
=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
Try

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

#### Dave Patton

##### Well-known Member

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

#### Muthukrishnan V

##### Board Regular
Thank you Sirs, Works fine.

#### Peter_SSs

##### MrExcel MVP, Moderator
Thank you Sirs, Works fine.
Glad you got a satisfactory result from the various suggestions. Thanks for letting us know.

Replies
15
Views
360
Replies
5
Views
164
Replies
7
Views
115
Replies
9
Views
388

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

### 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