*Working on a report, investment plan or any other dataset with dates, you may often need to sum numbers within a specific period. This tutorial will teach you a quick and easy solution - SUMIFS formula with date range as criteria.*

On our blog and other Excel forums, people often ask how to use SUMIF for date range. The point is that to sum between two dates, you need to define both dates while the Excel SUMIF function only allows one condition. Luckily, we also have the SUMIFS function that supports multiple criteria.

To sum values within a certain date range, use a SUMIFS formula with start and end dates as criteria. The syntax of the SUMIFS function requires that you first specify the values to add up (sum_range), and then provide range/criteria pairs. In our case, the range (a list of dates) will be the same for both criteria.

Considering the above, the generic formulas to sum values between two dates take this form:

Including bound dates:

SUMIFS(*sum_range*, *dates*,">="&*start_date*, *dates*, "<="&*end_date*)

Excluding bound dates:

SUMIFS(*sum_range*, *dates*,">"&*start_date*, *dates*, "<"&*end_date*)

As you can see, the difference is only in the logical operators. In the first formula, we use *greater than* *or* *equal to* (>=) and *less than or equal to* (<=) to include the lower and upper bound dates in the result. The second formula checks if a date is *greater than* (>) or *less than *(<), leaving out the start and end dates.

In the table below, suppose you want to sum projects that are due in a particular date range. To have it done, we enter the start date in F1, the end date in G1, and use the following formula to add up the budgets in B2:B10 between these two dates, inclusive:

`=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1)`

If you prefer hardcoding a date range in the formula, then type a date right after the logical operator and enclose the whole criteria in quotation marks like this:

`=SUMIFS(B2:B10, C2:C10, ">=9/10/2020", C2:C10, "<=9/20/2020")`

To avoid possible mistakes, you can supply dates with the help of the DATE function:

`=SUMIFS(B2:B10, C2:C10, ">="&DATE(2020,9,10), C2:C10, "<="&DATE(2020,9,20))`

In situation when you need to sum data within a dynamic date range (X days back from today or Y days forward), construct the criteria by using the TODAY function, which will get the current date and update it automatically.

For example, to sum budgets that are due in the last 7 days **including todays' date**, the formula is:

`=SUMIFS(B2:B10, C2:C10, "<="&TODAY(), C2:C10, ">"&TODAY()-7)`

If you'd rather not include the current date in the final result, use the *less than* operator (<) for the first criteria to exclude today's date and *greater than or equal to* (>=) for the second criteria to include the date which is 7 days before today:

`=SUMIFS(B2:B10, C2:C10, "<"&TODAY(), C2:C10, ">="&TODAY()-7)`

In a similar manner, you can sum values if a date is a given number of days forward.

For example, to get a total of budgets that are due in the next 3 days, use one of the following formulas:

Today's date is included in the result:

`=SUMIFS(B2:B10, C2:C10, ">="&TODAY(), C2:C10, "<"&TODAY()+3)`

Today's date is not included in the result:

`=SUMIFS(B2:B10, C2:C10, ">"&TODAY(), C2:C10, "<="&TODAY()+3)`

To sum values within a date range that meet some other condition in a different column, simply add one more range/criteria pair to your SUMIFS formula.

For example, to sum budgets within a certain date range for all the projects that contain "tip" in their names, extend the formula with a wildcard criteria:

`=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1, A2:A10, "tip*")`

Where A2:A10 are the project names, B2:B10 are the numbers to sum, C2:C10 are the dates to check, F1 is the start date and G1 is the end date.

Of course, nothing prevents you from entering the third criteria in a separate cell too, and referencing that cell like shown in the screenshot:

When it comes to using dates as criteria for Excel SUMIF and SUMIFS functions, you wouldn't be the first person to get confused :)

Upon a closer look, however, all the variety of use cases boils down to a few simple rules:

If you put dates directly in the **criteria arguments**, then type a logical operator (>, <, =, <>) right before the date and enclose the whole criteria in quotes. For example:

`=SUMIFS(B2:B10, C2:C10, ">=9/10/2020", C2:C10, "<=9/20/2020")`

When a date is input in a predefined **cell**, provide criteria in the form of a text string: enclose a logical operator in quotation marks to start a string and use an ampersand (&) to concatenate and finish the string off. For instance:

`=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1)`

When a date is driven by **another function** such as DATE or TODAY(), concatenate a comparison operator and a function. For example:

`=SUMIFS(B2:B10, C2:C10, ">="&DATE(2020,9,10), C2:C10, "<="&TODAY())`

In case your formula is not working or producing wrong results, the following troubleshooting tips may shed light on why it fails and help you fix the issue.

If a seemingly correct SUMIFS formula returns nothing but zero, the first thing to check is that your dates are really dates, and not text strings that only look like dates. Next, make certain you are summing numbers, and not numbers stored as text. The following tutorials will help you spot and fix these issues.

When checking dates using SUMIFS, a date should be put inside the quotation marks like ">=9/10/2020"; cell references and functions should be placed outside the quotes like "<="&G1 or "<="&TODAY(). For full details, please see date criteria syntax.

A small typo in a budget could cost millions. A little mistake in a formula might cost hours of debugging time. So, when summing between 2 dates, check if the start date is preceded by the *greater than* (>) or *greater than or equal to* (>=) operator and the end date is prefixed by *less than *(<) or *less than or equal to* (<=).

For the SUMIFS function to work correctly, the sum range and criteria ranges should be equally sized, otherwise a #VALUE! error occurs. To fix it, ensure that all *criteria_range* arguments have the same number of rows and columns as *sum_range*.

That's how to use the Excel SUMIFS function to sum data in a date range. If you have some other interesting solutions in mind, I'll be really grateful if you share in comments. Thank you for reading and hope to see you on our blog next week!

SUMIFS date range examples (.xlsx file)

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Shared email templates

Category: Excel Tips

60+ professional tools for Excel

2019–2010 to do your daily work

2019–2010 to do your daily work

Incredible product, even better tech support…AbleBits totally delivers!

Deborah Bryant

Anyone who works with Excel is sure to find their work made easier

Jackie Lee

The best spent money on software I've ever spent!

Patrick Raugh

Ablebits is a fantastic product - easy to use and so efficient.

Debra Celmer

Excel is at its best now

Annie C.

I don't know how to thank you enough for your Excel add-ins

Jennifer Morningstar

Anybody who experiences it, is bound to love it!

Kumar Nepa

AbleBits suite has really helped me when I was in a crunch!

Nelda Fink

I have enjoyed every bit of it and time am using it

Christian Onyekachi Nwosu

It's the best $100 we've ever spent!

Mike Cavanagh

I love the program, and I can't imagine using Excel without it!

Robert Madsen

One word… WOW!

Dave Brown

Love the products!

David Johnston

It is like having an expert at my shoulder helping me…

Linda Shakespeare

Your software really helps make my job easier

Jeannie C.

Thanks for a terrific product that is worth every single cent!

Dianne Young

I love your product

Brad Gibson

Awesome!!!

Sheila Blanchard

## 64 responses to "Sum if between two dates in Excel: SUMIFS with date range as criteria"

I am trying to develop an Excel formula to obtain a count.

If an ID number appears multiple times in one column and the result “received” appears one or more times in a different column, what is the formula to count only 1 instance of the occurrence of the ID and “received”?

For example:

Column Column

F J

5596326 attempted

5596326 delivered

5596326 received

5596326 received

5596326 sent

5596326 Wrong Number

The ID number appears 6 times in column F, and “received” appears twicein column J. With a correct formula, the result should be 1.

What is the formula?

Thank you for any assistance.

Hi,

Not sure if I understood your ask here, but try below formula in H1.

=COUNTIFS($F$1:$F$6,F1,$G$1:$G$6,G1)

5596326 attempted 1

5596326 delivered 1

5596326 received 2

5596326 received 1

5596326 sent 1

5596326 Wrong Number 1

Very helpful. Thanks a lot.

I use the following file as the source of my SUM(IF(COUNTIFS formula, a revision of one used in your blog for "Sum if between two dates and another criteria":

Date Injury Location Gender Age Group Incident Type Plant Report Type Shift Department Incident Cost Days Lost

1-Jan-20 Multiple Male 25-34 Burn Iowa Near Miss Afternoon Painting $- 0.00

3-Jan-20 N/A Male 35-49 Vehicle Alabama Lost Time Day Fabrication $3,367 0.50

3-Jan-20 Eye Male 18-24 Cut Georgia Near Miss Day Administration $- 0.00

My formula [=SUM(IF(Data!$A$2:$A$515=DATE(2020,1,1)),1/COUNTIFS(Data!$F$2:$F$515,$A3,Data!$J$2:$J$515,">=100",Data!$A$2:$A$515,"="&DATE(2020,1,1))),0)]

It returns an unexpected value of 18 to display the number of incident each month at each plant which reported plant injury costs which exceed $100. For Iowa, the expected return value is 9 for the month of January.

i have a question for formula...

I have to made a report that contains different items. the report is based on another sheet which contains different items that had issued received , put on production on many dates. I have to made formula that allow range of dates of one particular items to be appear says in production line.

Please help me in this regards.

Hello!

Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.

I have what I'm assuming is a straightforward formula challenge that I can't seem to figure out.

I have a table of data with the headers "Date,START TIME, END TIME, USAGE, UNITS, COST". The START TIME and END TIME is an hour block of time (e.g., 0:00 & 0:59, up to 23:00 & 23:59).

In adjacent cells I have, say J17 & J18 with a pull down list of dates, that I want to use in a "greater than or equal to 02/01/2021 and less than or equal to 02/28/2021, in a referencing sub table if you will immediately adjacent to these date parameters, listed as "START TIME", "COST", and "USAGE".

In the cells under this sub table, I want to place formulas that do this:

* Given the date interval cells, search the main table's START TIME (categories = hour blocks) and sum the values for COST and USAGE.

Hello!

Unfortunately, without seeing your data it is difficult to give you any advice. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

I would like to do a similar thing but kind of opposite and was wondering if you can help.

We have a spreadsheet with events (multiple days) and numbers of staff e.g.

Start date End date Number of staff

1 Jan 21 3 Jan 21 5

2 Jan 21 2 Jan 21 3

5 Jan 21 10 Jan 21 10

What I would ideally like is to then have a second sheet with all the dates of the year in column A and then how many staff are working on that date so with the above it would look like

1 jan 21 5

2 jan 21 8

3 jan 21 5

4 jan 21

5 jan 21 10

6 Jan 21 10

Etc....

Is that possible?

Thanks

Andy

Hello!

I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

=SUMIFS(Sheet1!$C$1:$C$3,Sheet1!$A$1:$A$3,"<="&Sheet2!A1,Sheet1!$B$1:$B$3,">="&Sheet2!A1)

Hope this is what you need.

Thank you very much sir!

I have question for you as next;

exam: Start dates dest date Year month day

1983-02-07 1989-03-19 6 1 12

1989-04-03 1994-10-08 5 6 5

1994-10-08 2000-12-31 6 2 23

sum() ? ? ?

I want to find and to know it 8 sum of year , months, days by use excel function

Thanks,

Gans

Hello!

Sorry, I do not fully understand the task.

What result do you want to get exactly? Please describe your problem in more detail.

My Start date is in one collume and my end date is in another - i cant seem to get this to work?

Example 1: =SUMIFS(Sheet2!$Q:$Q,Sheet2!$I:$I, ">="&Sheet1!B4, Sheet2!$J:$J, "="&1/4/2021,Sheet2!J:J,"<="&1/4/"2021")

Hello!

It is very difficult to understand a formula that contains unique references to your workbook worksheets. I recommend paying attention to the syntax of the SUMIFS function. I kindly ask you to have a closer look at the following paragraph of the article above — SUMIFS date criteria syntax.

Sheet2!$J:$J, "=1/4/2021",Sheet2!J:J,"<=1/4/2021"

I hope it’ll be helpful.

Sorry about that! I did reread it again and I have attempted a few different things.

let me try by using a simple version of what I would like to accomplish: =SUMIFS(J:J, H:H, ">="&B6, I:I, "<="&B6) H = is my Start Date, I = my End Date, J is the values i would like to sum if they are inside the date listed in B6

I am tracking productivity for a department and want to provide weekly updates to our team in the form of previous week averages. I'm trying to create a quick calculator where I can simply change the Start/End Dates and it recalculates the averages automatically.

The formula I have below keeps returning @DIV/O! errors. C2:KW2 is the portion of the row where daily productivity will be input. C1:KW1 is the row with each days date in it. A36 and A39 are the cells that are dynamic and where I would input the date range I am trying to calculate.

=AVERAGEIFS(C2:KW2, C1:KW1, ">="&A36, C1:KW1, "<="&A39)

Hello!

If there are no dates in the C1: KW1 range that match your conditions, then the formula will return an error. Check how you enter your dates. Perhaps they are written in the form of text.

From an invoicing perspective, each day there is a charge for service (prorated amount) based on a monthly amount.

I would like to calculate the dollar amount for a service for 'Date A' through 'Date B' (both Date A & B are billable days).

The spreadsheet to include a user defined 'Date A' (ie: 01/15/21), 'Date B' (ie: 04/16/21) and 'Monthly $' (ie: $10.00)

January there are 17 billable days (01/15/21 thru 01/31/21) * 0.0323 (daily prorate) = $5.48 (prorated amount of the 'Monthly $')

February there are 28 billable days = $10.00 ('Monthly $')

March there are 31 billable days = $10.00 ('Monthly $')

April there are 15 billable days (04/15/21 thru 04/30/21) * 0.5000 (daily prorate) = $5.00 (prorated amount of the 'Monthly $')

A1 = 'Data A'

A2 = 'Date B'

A3 = 'Monthly $'

A4 = ? - hoping for a formula here

Amended:

April there are 15 billable days (04/01/21 thru 04/15/21) * 0.0333 (daily prorate) = $5.00 (prorated amount of the 'Monthly $')

Hello!

If I got you right, the formula below will help you with your task:

=DATEDIF(A1,A2,"m")*A3 + (EOMONTH(A1,0)-A1+1)*A3/(EOMONTH(A1,0)-EOMONTH(A1,-1)) + (A2-EOMONTH(A2,-1))*A3/(EOMONTH(A2,0)-EOMONTH(A2,-1))

You can learn more about DATEDIF function in Excel in this article on our blog.

Hope this is what you need.

Amended:

The spreadsheet to include a user defined 'Date A' (ie: 01/15/21), 'Date B' (ie: 04/15/21) and 'Monthly $' (ie: $10.00)

Is there any way of using SUMIF combined with days of the week? I have dates which display as the 3-character day of the week (Sun, Mon, Tue, etc.). I'd like to use SUMIF on those days of the week. If I change the date to a 3-character string, then I can make SUMIF work, but I'd rather not.

Hello!

I am not sure I fully understand what you mean.

Are your days of the week written as date or as text?

Describe in more detail what you want to do.

Give an example of the source data and the expected result.

It’ll help me understand it better and find a solution for you.

Hi, I can't get this to work when hvaing the date range in colums. Here's the formula I'm using:

=SUMIFS(v0[@[2021-04-01]:[2021-04-30]], $C$2:$AF$2, ">="&Report!$B$1, $C$2:$AF$2, ",<) signs, it return 0 even if the = is kept. So it is something with the range config that is not correct.

Can you help?

Note, date format is correct (Scandinavian pc).

the comment field is not working, it cuts part of the text...

Hi,

Insert a space after the < sign. Or write this sign in words.

The text was not published in full

Hi, I can't get this to work when having the date range in columns. Here's the formula I'm using:

=SUMIFS(v0[@[2021-04-01]:[2021-04-30]], $C$2:$AF$2, ">="&Report!$B$1, $C$2:$AF$2, ",<) signs, it return 0 even if the = is kept. So it is something with the range config that is not correct.

Can you help?

Note, date format is correct (Scandinavian pc). Resending the post as only part of the text were published.

No point in trying to use this for questions any longer, as the comment function unfortunately doesn't work properly

Hi, I can't get this to work when having the date range in columns. Here's the formula I'm using:

=SUMIFS(v0[@[2021-04-01]:[2021-04-30]], $C$2:$AF$2, ">="&Report!$B$1, $C$2:$AF$2, ",<) signs, it returns 0 even if the = is kept. So it is something with the range config that is not correct.

Can you help?

Hi,

I cannot validate your formula as it contains unique references. Explain what the expression means v0[@[2021-04-01]:[2021-04-30]] ?

Include an example of the source data and the result you want to get. It’ll help me understand the problem you faced better and help you.

As I been posting, all text is not published. But trying once more, now with the missing part only.

=SUMIFS(v0[@[2021-04-01]:[2021-04-30]], $C$2:$AF$2, ">="&Report!$B$1, $C$2:$AF$2, ",<) signs, it return.....

no, still not publishing...

=SUMIFS(v0[@[2021-04-01]:[2021-04-30]], $C$2:$AF$2, ">="&Report!$B$1, $C$2:$AF$2, "<="&Report!$C$1)

The sheet (v0) has a table where the headers are dates (the entire April in this case) located in columns C to AF on row 2. Each row in the table contains numbers for each date. The sheet "Report" contains a start date at B1 and an end date at C1.

When using this formula it returns 0 and no error message. As a test, I changed the start/end date to the same date, and changed the formula to only exact match (=), the I get a result for the date in question. But if I add the higher/lower (>,<) signs, it return 0 even if the = is kept. So it is something with the range config that is not correct.

Note, date format is correct (Scandinavian pc).

Hope it makes sense as I had to publish in separate comments

Hello!

Unfortunately, without seeing your data it is impossible to give you advice.

I'm sorry, it is not very clear what result you want to get. Could you please and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

We'll look into your task and try to help.

I try to get a formula for a calculation. I have expense per month equally Exp:$500 between a range date, Jan 21 - Jun-21. I have some condition, if each expense less then $1000, then Cell A5 will equal to the sum of all expense add up from Jan-21 to Jun-21. but if the expense per month is bigger then $1000, then the Cell A5 will equal to each month of the expense (April 2021).

Col A B C D E F G

Months January-21 February-21 March-21 April-21 May-21 June-21

Expense 500 500 500 500 500 500

Cell A5=April-21

=IF(B3:G3<1000,SUM(B3:G3),SUMIF(B3:G3,B2:G2,A5)) is not working. can you please help me on this?

Hello!

Sorry, I do not fully understand the task. What result do you want to get exactly?

What does the “then the Cell A5 will equal to each month of the expense (April 2021)” phrase mean?

Your question is not entirely clear, please specify.

For the test data i'm using the column headers are dates and i'm trying to calculate the sum for the each month, which formula can I use?

Hi!

Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

Just to say "Thank You! It helps!" our charming Excel wizard! :) .... and to ask "Can you do "AND" logic for two or more conditions with "SUMIF()"?

Hi!

You can learn more about Excel SUMIFS and SUMIF with multiple criteria in this article.

Hi , I am getting wrong result in Sumif. Below is the data set

B1:x1=Week 49 Week 50 Week 51 Week 52 Week 53 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 Week 11 Week 12

Weekly jobs done B2:X2 =0 0 0 0 1 0 1 0 2 1 0 1 1 1 0 2 1

Criteria= Week 4

Formula used =SUM(B2:F2)+SUMIF(I1:X1,"<="&A3,I2:X2)

Jobs done till Week 4= 7 result by formula

result should be 4

Thanks in advance

priyank

Hello!

You can use this formula:

=SUM(B2:F2)+SUMIF(G1:X1,"<="&A3,G2:X2)

This formula gives the result 4

I'm trying to summarize some product counts by different date ranges. For example, in column C, I have several products listed from rows 13 thru 50 with some products in a similar category repeating and the counts are in columns to the right with dates as headings (ie: 6/28/21, 6/29/21, thru 12/31/21). I need to summarize by product and then by different date ranges (ie: weekly vs monthly) but am not sure the best way to do this in Excel. All the examples I see have all the data in columns including the dates, but in my case I don't have that.

GL Acct 06/28/21 06/29/21 06/30/21 7/01/21 07/02/21 07/03/21....

Filling 1 10 15 12 5 6 4

Filling 2 4 1 3 20 11 2

So, I need to be able to sum the Fillings into 1 number for Jun that includes the numbers from 6/28 and 6/29 and then summarize the Fillings into 1 total for Jul that includes 7/1 thru 7/3.

Any help is appreciated.

Kat

Hello!

Take a look at this comment. Perhaps this is the answer to your question.

If something is still unclear, please feel free to ask.

Hello,

I'm having right trouble with this formula. I can't work out what i've done wrong! I'm trying to calculate price by customer within a set date period.

=SUMIFS(G8:G20, B8:B20, "=>"&B5, B8:B20, "=<"&C5, M8:M20, "="&A5)

Can someone identify what's wrong with it?

Column G is price

Column B is date

Column M is customer

A5 is customer name

B5 is the start date

C5 is the end date

I've checked the dates are in date format and the numbers are in number format

I've interchanged the signs

I've added and removed the "="& for the customer name

I've added spaces and removed them.

None of these have resulted in anything. The Formula box remains blank.

Help please!!

Hello!

Here is the article that may be helpful to you: How to sum if between two dates in Excel.

=SUMIFS(G8:G20, B8:B20, ">="&$B$5, B8:B20, "<="&C5, M8:M20, "="&$A$5)

“=>” is wrong.

I'm not sure if the summation of prices makes sense.

S.NO DATE AMOUNT

1 19-04-2021 -8,930.00

2 20-04-2021 20,250.00

3 22-04-2021 7,047.20

4 23-04-2021 -27,000.00

5 26-04-2021 -4,200.00

6 27-04-2021 -

7 28-04-2021 10,500.00

8 29-04-2021 -12,000.00

9 30-04-2021 -3,200.00

How to calculate total -ve value of every month; +ve value of particular month & count for zero by sumifs & countif functions.

Please help me. I am waiting for your solution.

Thanks

prakash

It would be great to get some help if possible. I have a spreadsheet which tracks transactions and values between certain dates, and I am trying to do a sum dependent on a certain date criteria i.e how much value was gained in the month of April.

I followed your steps, and have a formula of:

=SUMIFS('EXP April-June 2021'!AF2:AF5131,'EXP April-June 2021'!I2:I5131,">="&DATE(2021,4,1),'EXP April-June 2021'!AF2:AF5131,"<="&DATE(2021,4,31))

And it returns a figure for me, but it's about £10m short of what it should be when I use the highlight & count approach on those dates.

I cannot work out why it would be doing this. All of the dates are formatted as dates, and the values are formatted as currencies.

I have tested this on the results for May & June so far, and the formula works in that it returns a different figure for each month, and updates as I add more to the source data, but doesn't calculate the correct figure currently.

Nevermind... I fixed it. I accidentally put the value data set in the formula twice rather than the date data set. Thanks for your guides!

Hello!

I can't see your data but I guess the problem is in DATE(2021,4,31). No such date exists. The formula DATE(2021,5,31) returns May 31, 2021 00:00:00. Therefore, the last day of the month is ignored. Use DATE(2021,6,1) instead of DATE(2021,5,31). Perhaps this will help.

I am trying to do a sumifs formula on a range of dates, however It keeps returning a zero

05 May 2020 14,256.00

01 May 2020 12,356.00

04 May 2020 12,548.00

02 May 2020 12,536.00

06 June 2020 12,586.00

08 June 2020 12,365.00

10 May 2020 12,356.00

Find Data between ranges: 01 May 2020 - 10 May 2020

=SUMIFS(C2:C8,B2:B8,">''&F2",B2:B8,"<''&G2")

Hello!

The formula below will do the trick for you:

=SUMIFS(C1:C8,B1:B8,">"&F2,B1:B8,"<"&G2) I hope it’ll be helpful.

It says that there is a problem with this formula

I have a cash flow on one sheet of an excel spreadsheet with a balance for each month of the year. each month aligns to UK tax year so April runs from the 6th of April to the 5th of May and so on. Using The TODAY() function I am trying to the show the current monthly balance from the cashflow on another sheet.

Can you assist?

Hello!

If I understand your task correctly, the following formula should work for you:

=SUMPRODUCT(D1:D15,--(C1:C15>=EOMONTH(TODAY(),-1)+6),--(C1:C15<=EOMONTH(TODAY(),0)+5))

You can learn more about EOMONTH function in Excel in this article on our blog.

Hello,

I am sincerely hoping you can help me. I'm trying to find a formula to add in monthly rent on the first day of the month to an equation which will then subtract it from the amount that they paid that month to give me a running total of the amount that they owe

Rent Due Running Total January Febraury

of Money Owed

1000 ?? 850 900

Hi, I'm using a sumifs function for each month's totals with the year referenced in a separate sheet. It works great for every month except Feb/March. I used the range "="&DATE(DASHBOARD!$C$2,2,1),$B:$B,"<="&DATE(DASHBOARD!$C$2,2,29))

Not sure why half my message is cut off. For Feb Entries I allowed "<=" 2-29 to allow for leap years but now entries for Mar 1 show up in BOTH Feb. and Mar. totals. How do I include all dates between the beginning and end of each month allowing for leap year without problems? Thanks.

Hello!

To determine the end date of the month, use the function EOMONTH :

=EOMONTH(DATE(DASHBOARD!$C$2,2,1),0)

Read more here.