*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.

## How to sum if between two dates in Excel

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 the threshold dates:

*sum_range*,

*dates*,">=

*start_date*",

*dates*, "<=

*end_date*")

Excluding the threshold dates:

*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 threshold 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, inclusive. To have it done, use this formula:

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

If you'd rather not hardcode a date range in the formula, then you can type the start date in F1, the end date in G1, concatenate the logical operators and cell references and enclose the whole criteria in quotation marks like this:

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

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

## Sum within a dynamic range based on today's date

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

## Sum if between two dates and another criteria

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:

## SUMIFS date criteria syntax

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

## Excel SUMIFS between dates not working

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.

### Check the format of dates and numbers

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.

### Use the correct syntax for criteria

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.

### Verify the formula's logic

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* (<=).

### Make sure all ranges are the same size

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!

## Practice workbook for download

SUMIFS date range examples (.xlsx file)

## 209 comments

Hi! You can see the correct SUMIFS formula with dates in the first section of the article above: How to sum if between two dates in Excel.

For example:

=SUMIFS($D$18:$D$1516,$H$18:$H$1516,">=01/01/2022",$H$18:$H$1516,"<=12/31/2022")

You can also find useful information in this article: Excel SUMIFS and SUMIF with multiple criteria – formula examples.

With much help from this board - i was finally able to sum all amounts related to a specific category

Amazon =SUMIF($C$18:$D$1516,G18,$D$18:$D$1516) $481.38

APPLE =SUMIF($C$18:$D$1516,G19,$D$18:$D$1516) 56.38

AUTO GAS =SUMIF($C$18:$D$1516,G20,$D$18:$D$1516) 1662.59

ETC

But now I want to ALSO sum WITHIN a date range....ie all Amazon purchases for 2022.. I input :

=SUMIF(H19,">=01/01/2022"&""<=12/31/2022)

I initially only had one " after the & sign but a window popped up saying (we found a typo in your formula and tried to correct it - do you want to accept this correction..I said yes)...but I still got a -0- result.

What am I doing wrong - what SHOULD the formula be?

Hi,

I have an excel with two tabs, one has the daily report on it with numbers and the 2nd tab needs to capture data weekly, if the month is ending 2 days in a week it has to be 2 days data, if it is 5 days a week then it has to be 5 days a week. Currently i am manually going back and forth to each tab and linking the file. is there any formula to auto sum weekly data from daily tab. please let me knoe

Hi! You can sum data by the number of the month and the number of the week. If the month and week numbers are written to additional columns using the MONTH and WEEKNUM functions, you can use the SUMIFS function. The formula might look like this:

=SUMIFS(B1:B20,E1:E20,18,F1:F20,5)

The best and easiest to follow explanation of using sumif between two dates and another criteria. Thank you, thanks you, thank you :D I've now got a sheet that does exactly what I want it to do.

Thanks

Would appreciate someone's help...

These two SumIf formulas in the same spreadsheet produce different results:

=SUMIFS($AC2:$AC4655,$D2:$D4655,">=06/01/2023 ??:??",$D2:$D4655,"=06/01/2023 ??:??",$D2:$D4655,"<=06/31/2023 ??:??") yields $21,344.95

My question is, since I am not inputting a 6/31 date, why should the sums be different?

Cheers!

Tom

Don't know what happened there. The two formulas should be...

Would appreciate someone's help...

These two SumIf formulas produce different results:

=SUMIFS($AC2:$AC4655,$D2:$D4655,">=06/01/2023 ??:??",$D2:$D4655,"=06/01/2023 ??:??",$D2:$D4655,"<=06/31/2023 ??:??") yields $21,344.95

My question is, since I am not inputting a 6/31 date, why should the sums be different?

It's still screwed up. Not what I am inputting to this replay box.

Hi! I can't check your formula because I don't have your data. Remove the ??:??

Also keep in mind that date 06/01/2023 means 06/01/2023 00:00:00.

All the necessary information is in the article above.

The data in column AC are $ amounts in currency format. The data in column D are dates in text format: 06/01/2023 14:15.

Appreciate your considering the issue!

Looking at my original post I se only one formula managed to get pasted in. Weird. The formula I thought would be proper is:

=SUMIFS($AC2:$AC4655,$D2:$D4655,">=06/01/2023 ??:??",$D2:$D4655,"=06/01/2023 ??:??",$D2:$D4655,"<=06/31/2023 ??:??") yields $21,344.95

Many thanks!

Tom

So I'll try this. The first formula, the one I believed would be correct is:

=SUMIFS($AC2:$AC4655,$D2:$D4655,">=06/01/2023 ??:??",$D2:$D4655,"<=06/30/2023 ??:??"

This yields $20,414.95

Ok, since that posted correctly, here's the second formula, where only end date has changed:

=SUMIFS($AC2:$AC4655,$D2:$D4655,">=06/01/2023 ??:??",$D2:$D4655,"<=06/31/2023 ??:??")

This yields $21,344.95

But I do not have any 06/31/2023 entries in my D column data

Hi! You cannot do any calculations with text strings. Convert text to date in a separate column. To do this, you can use the methods described in this manual: How to convert text to date and number to date in Excel. For example:

=DATEVALUE(LEFT(D2,10))

Then use the data from that column with the dates in the SUMIFS formula.

How do I sum based on a dynamic date range? I'm trying to build a mortgage calculator where the mortgage will commence on a certain day (say today) but clients keep asking me how much will they pay in principal and interest within that calendar year (i.e. Dec-31 20xx). How would I program the SUMIF so that I can get interest and principal outputs for that calendar year (i.e. mortgage starts in any month).

Keep in mind the excel sheet has a row for each month already so I know the IPMT and PPMT for each month, but the summation is where its tricky for me. I'd like to program it so that whichever calendar year we're in (i.e. 2023, 2024, 2025), the SUMIF function would output the summation of the interest paid for that calendar year only

I'm trying to avoid hard coding dates or use the (=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1, A2:A10, "tip*") example above as that would require some manual inputting of dates. It'd be ideal if Excel could pick up the first month (e.g. March, or whichever month the property is closing in) and calculate the interest automatically up until December, and the same thing for the next calendar year, and keep continuing and providing 30 outputs for the 30-year amortization period (the last year will be January to February based on the example above).

Hi! If I understand your task correctly, to automatically get the date of the last day of the current year in cell G1, use the DATE function.

=DATE(YEAR(TODAY()),12,31)

Hope this is what you need.

Hi Alex, thanks for looking into this. Unfortunately, it doesn't answer my question. I'm not trying to output a date but rather a summation of a column based on a dynamic date.

The date range will be dynamic but how do I loop this into the summation formula??

- Basically if the mortgage starts in May 2023 then I'd like to do a summation of my interest and principal columns up until December 2023 (i.e. the first year).

- Then I would like to do a straightforward calculation January to December (for the next 28 years, i.e. 2024 to 2052).

- The last year's calculation would be dynamic once again as it will only be from January to April only (the 30th year i.e. 2053)

I hope the above provides better clarification

Hi! Maybe this article will be helpful: Excel dynamic named range: how to create and use.

Or you can try writing the start and end dates in separate cells and sum the data using the SUMPRODUCT formula as described in this guide: Excel SUMPRODUCT function with multiple criteria.

How to sum product wise 250+ sheets Day wise. Indirect Sumifs within a dynamic range based on today's date

Hi! The SUMIFS formula cannot calculate the sum of data on 250 sheets. I recommend paying attention to Consolidate Sheets tool to calculate the sum on multiple worksheets. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

Indirect Sumifs within a dynamic range based on today's date

Hi! As it's currently written, it's hard to tell exactly what you're asking

Good afternoon!

I'm having trouble with some pre-existing spreadsheets I have to work with. I copied over some dates from another spreadsheet that were in a different format (01-oct) to a sheet where they want it written like Sunday 01 October.

I copied the data over in the original format and then formatted the whole column to be dddd dd mmmm to show it like above. However that has now thrown my formula out on the second page. I have page that is set to count figures for "October" but it doesn't see the ones I've reformatted now.

Any help?!

Hi! The date format cannot change the date value. Check what values you copied. Perhaps the second table had a different date format and when copying the date October 1 turned into January 10.

The dates are being copied over correctly and when I format it's showing the date written out fully as intended - Wednesday 11 October but when you click in the formula bar it shows the date as 11/10/23. Due to this my other formulas won't count this box as it doesn't see it as showing "October".

Hi! Read more about what a date is in Excel, and what a date is in a specific format, here: How to change date format in Excel and create custom formatting. If your formulas require the name of the month in the cell, convert the date to text in the desired format as described in this guide: Convert date to text in Excel - TEXT function and no-formula ways.

Hello, I'm having my laptop date as UK and my friend's laptop date is in USA. Anytime we exchange report. The whole formula get disjointed. Any help please

Hi! Use "<="&DATE(2020,9,20)" instead of "<=9/20/2020". Then the date format will not matter. For more information, please read: Excel DATE function.

Hi sir i need help how do i sum amount of sales based on specify date and account such as total of cash sales, debit card sales from 1/09/2023 to 6/09/2023.I have two different sheets for sales sheet and the summary sheet which i want to summarize based on date that i key in the sales sheet...would really appreciate any help from you sir.

Hi! Calculate the amount of sales based on the specified date and account on each sheet, and then summarize. All the necessary information is available in the article above. I also recommend to pay attention to the Consolidate Sheets tool and Combine Sheets tool. There is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

Thanks for this, it works well in the desktop version, but apparently not in the web version!

Hi

I'm facing an issue and would like to know your suggestions.

So I have two sheets, one has names of accounts and their contract dates, the second sheet is a pivot table that has the names of the accounts (rows) and the month-year (columns) as well as their sales in 3 branches (columns).

I want to get the sales sum for each branch starting from the contact date for all accounts in the first sheet, would it be possible?

Hi! I can't offer you the formula because I don't have your data. To find the sum over multiple criteria, you can use the SUMIFS function and the guidelines in the article above. You can also use the SUMPRODUCT function and this guide: Excel SUMPRODUCT function with multiple criteria.

I hope it’ll be helpful. If something is still unclear, please feel free to ask.

Hi,

I am trying to create a monthly calendar for equipment onsite, I have the list of equipment required, a date range, days onsite, quantity of each equipment required. I have tried sumif, sumifs, vlookup, xlookup. I want my sheet to revert back to the data table, then populate each cell with a count of the total amount of that equipment onsite for that month.

On my monthly table of equipment in column A I have the list of equipment, then row 2 mm-yy for each column. On my data table I have a list of areas, then equipment list for each area, a mobilise and demobilise column for dates mm-yy format, days onsite column, qty of each piece of equipment in each area.

Hi! I can't give you the formula because I don't have your data. To calculate the number of values by condition, use the COUNTIFS function.

hi, i am trying to add up the hours worked by a person. this is the formula i am using

=SUMIFS('timesheets data dump'!H:H,user,review!K1,date,">="&review!M1,date,"<="&review!O1)

user is named range with their clock in id

date is a named range of the date of the clock in entry

timesheets data dump'!H:H is number of hours that entry

review!K1 is the from date

review!O1 is the to date

the number of hours is set as a number format

all date cells are formatted as date in the same date format

however if i put from:20/06/23 to:20/06/23 it calculates as 0 even though there is an entry for that day

also if i put from:20/06/23 to:21/06/23 it only adds the hours for the 20/06/23 and does not include the 21st even though i have said less than or equal to.

i would really appreciate if someone can see where i have gone wrong

Hi! I've answered many times on the blog that a date without a time means 00:00:00. So 20/06/23 to: 21/06/23 means 20/06/23 00:00:00 to: 21/06/23 00:00:00. The date 21/06/23 is not in your date range.

Thank you, adding +time(23,59,59) on the end date fixed it and it now works perfectly. i apologize that i didn't see your previous ones about the time default.

Sorry if this is covered somewhere and I've just not seen it after crawling through comments on six articles.

We are trying to generate a 'days in production' field using dates which are input into column A (start) and D (finish).

We need this number to give us the number of days something has been in production while it's still being produced (so days up to today) for jobs that haven't got a finish date (so the cell is blank). We're not bothered about counting only workdays or anything fancy - just purely want to know how many days it's been since the job started, whether it's finished or not.

Can this actually happen or do we need to be inputting today's date through the D column automatically (and constantly) until jobs are completed to get the number in question?

Thanks for your help.

Hi! If I understand your task correctly, try the following formula:

=IF(ISBLANK(D1),TODAY()-A1,D1-A1)

I hope I answered your question. If something is still unclear, please feel free to ask.

Thank you SO MUCH.

With the following formula, I use in a budget form to pull data from another sheet that is my check register. The formula works but for date on the transaction, I have to put 1/1/23 for every entry for January. How would I need to change the formula to include to find a transaction by date range?

The ,$H$2 is where I would like to get the date range. The checking!$h$:$h$ is where sumifs criteria for the amount, checking!$C$C$ criteria for the date, and checking!$E$E$ is the criteria for the category. I would like to be able to enter a transaction in the check register for the month of January for the day of the transaction instead of just putting 1/1/23 for every transaction.

=SUMIFS(Checking!$H:$H,Checking!$C:$C,$H$2,Checking!$E:$E,E23)

Hi! If I understand your task correctly, pay attention to the following paragraph of the article above: Sum if between two dates and another criteria. It covers your case completely.

Hi guys

I have an issue that I can't seem to resolve regarding difference between dates:

Works fine:

=SUMIFS(NetValue, ProjectNo, "Proj101", DueDate, A1)

=SUMIFS(NetValue, ProjectNo, "Proj101", DueDate, "="&A1)

Does NOT work (returns 0):

=SUMIFS(NetValue, ProjectNo, "Proj101", DueDate, ">="&A1)

Seems like these is some sort of issues with the ">=" formatting in the above, so I'd really appreciate your assistance.

Thanks

Hi! I don't have your data, so I can't check this formula. But I don't see any errors in the formula. Check your data and cell A1.

Hi,

I have 2 separate worksheets, one with a list of item codes and what stock I am holding. The other inbound deliveries (as below). As i need a view of current stock and inbound volume to see how many days coverage I will have, what formula would I use to look up the item code from the stock sheet and calculate total volume ordered on the inbound deliveries but ignore the volume if beyond the delivery date (it will be a continuous list which will include future inbounds).

Supplier Item Item Description Order Date Qty Ordered Delivery Date Received

ABC 123 Socks Size 11 23/04/2023 10 28/04/2023 10

DEF 456 Socks Size 12 24/04/2023 10 27/04/2023 0

DEF 456 Socks Size 12 25/04/2023 10 27/04/2023 10

ABC 789 Socks Size 13 26/04/2023 10 28/04/2023 10

DEF 987 Socks Size 14 27/04/2023 10 29/04/2023 0

ABC 123 Socks Size 11 28/04/2023 10 30/04/2023 10

Thanks

Hi!

From your description, I can't understand exactly what you want to count. I think these two articles will help you: Excel SUMIFS and SUMIF with multiple criteria and SUMPRODUCT function with multiple criteria.

If you accurately and in detail write down what result you want from your data, I will try to give more detailed advice.

I have a Start Date and End Date for the activities along with the required manpower. Then I want to use the SUMIF function to add the manpower within two separate dates (i.e. Week starting Dates) that link with the Start and End Date of the Activity.

Start Finish Man Power Reqd.

22-Mar-24 18-Apr-24 1

17-May-24 28-Jun-24 3

24-Jun-24 5-Aug-24 3

16-Jul-24 26-Aug-24 3

16-Apr-24 29-Apr-24 1

27-May-24 7-Jun-24 1

17-Sep-24 30-Sep-24 1

1-Oct-24 14-Oct-24 1

16-Oct-24 29-Oct-24 1

8-Jan-25 14-Jan-25 3

Week Starting Dates Manpower Requirement

3/18/2024 ?

3/25/2024 ?

4/1/2024

4/8/2024

4/15/2024

4/22/2024

4/29/2024

5/6/2024

5/13/2024

5/20/2024

5/27/2024

6/3/2024

6/10/2024

6/17/2024

6/24/2024

7/1/2024

7/8/2024

7/15/2024

7/22/2024

7/29/2024

8/5/2024

8/12/2024

8/19/2024

8/26/2024

9/2/2024

9/9/2024

9/16/2024

9/23/2024

9/30/2024

Can someone please help me with this?

Hi!

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

Formula example:

=SUMIFS(C1:C10,A1:A10,"<="&$D$1,B1:B10,">="&$D$1)

Thank you so much!

This code is calculating only if the date like that 01/04/2023 02/04/2023 but if I want to get data for today only and both dates are on the same day still gives me all the data

=SUMIFS(T_Re_Sales[Qty],T_Re_Sales[Products],[@[Item Name]],T_Re_Sales[Date],">="&$F$8,T_Re_Sales[Date],"<="&EOMONTH($H$8,0))

Hi!

It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work. Note, however, that the EOMONTH function always returns the last date of the month. For your data it is 30/04/2023.

I'm hoping this thread is the correct one based on my SUMIFS question.

I'm using this formula =SUMIFS(H19:H2261,F19:F2261,">="&A4,F19:F2261,"<="&B4)

H19:H2261 represents dollar amounts for Selling Fees

F19:F2261 represents a category for a product

A4 and B4 Represent a predetermined date range where A4 is the 1st day of the month and B4 is the last day of the month

The formula currently works just fine, my question is, how do I go about adding a second column of dollar amounts to the formula, let's say Payment Processing Fees? This additional column would be represented as J19:J2261 for example.

In short, I'd like this formula to also add the dollar amounts of an additional column of fees still within the same parameters of the formula listed above. I've tried several variations to no avail. Any assistance would be greatly appreciated!

Hello!

SUMIFS function can calculate the conditional sum in only one range. Add another SUMIFS formula for the second range.

=SUMIFS(H19:H2261,F19:F2261,">="&A4,F19:F2261,"<="&B4) + SUMIFS(J19:J2261,F19:F2261,">="&A4,F19:F2261,"<="&B4)

I hope I answered your question.

Yes, that did it!!! Thank you so very much, I wish I had known you could place more than one SUMIFS statement on one line. Thank you so very much!!!

Hi!

I'm trying to calculate sort of the inverse. I have a table of open orders with a booking date, payment date and the total cost per order. I want to calculate the cumulative of all open orders in each given date.

Example order table:

Order N Order Date Payment Made Cost

1 01/01/2023 02/01/2023 1

2 01/01/2023 03/01/2023 4

3 02/01/2023 03/01/2023 2

4 03/01/2023 04/01/2023 3

What I need:

Date Open Order Value

01/01/2023 5

02/01/2023 6

03/01/2023 3

04/01/2023 0

Anyone know how I can SUM orders which include the given date within their range, instead of the inverse?

Thanks!

Hi!

All the necessary information is in the article above.

=SUMIFS($D$1:$D$4,$B$1:$B$4,"<="&B1,$C$1:$C$4,">"&B1)

Ah right, Thank you! Did not see your comment.

Figured it out.

I ended up using:

=SUMIFS($E$1:$E$3871,$C$1:$C$3871,""&J2)

Where the first range is the total cost I wanted to calculate and then there is the first date filter and the second based on J2, which is the date in which I want to see the total cost accumulated.

I'm trying to sum spending in the most current week starting from Sunday. Column A contains the amounts spent, Column B contains the exact time.

I can sum the spending in the last 7 days with sumif(B2:B10000,">"&NOW()-7,A2:A10000). However, when I replace &NOW()-7 by &TODAY()-WEEKDAY(&TODAY(),2) I get a Formula Parse Error. What's the correct way of doing this?

Hi!

This formula works for me. But I don't see your data.

=SUMIF(B2:B10000,">"&TODAY()-WEEKDAY(TODAY(),2),A2:A10000)

I am trying to figure out a simpler way to see if a date range has any dates between a set date range without writing out a long and complicated formula.

Example:

Set date range : 1/1/2023 - 2/28/2023

Date range checking: 2/1/2023 - 3/30/2023

The set date range will change monthly. It is to verify if I have any promos within a date range.

Hi!

All the necessary information is in the article above.

Yes, Kimberly! I'm working with hotel room block budgets and multiple room rates based on specific nights within a larger date range and struggling to find a solution. COUNTIF and SUMIF will only count in single increments. I'm trying to calculate counting the total nights within a variety of date price ranges based on a guests' TOTAL room stay length.

For example:

Column A (Guest Name)

Column B (Check In Date): 1/30/2024

Column C (Check Out Date): 2/14/2024

However, 1/30-2/7 is $200/night, 2/8-2/11 is $500/night, and 2/12-2/14 is $300/night. So what formula would return total nights within each of the 3 mini date ranges, based on the OVERALL date range?

Hi! You can write dates and costs in 2 separate columns. For example:

1/30/2023 200

2/1/2023 200

.....

2/8/2023 500

and so on.

To calculate the cost, use the formula recommended in the article above. Dates can be replaced by references to date cells.

=SUMIFS(B2:B20,A2:A20,">="&DATE(2023,1,31),A2:A20,"<="&DATE(2023,2,14))

Hi, I'm not sure if any advice has already addressed this but i figured I'd still ask because I'm having issues. I'm trying to sum the total cost of items if today is between the start and end date of contracts, so my chart is set up like this

A B C D E F G H

ID # Manufacturer Model Asset Value Contract # Contract Vendor Start Date End Date

1 Manufacturer 1 Model 1 $100,000.00 12345 Equipment Repair Company 1 9/30/2020 9/30/2024

2 Manufacturer 2 Model 2 $100,000.00 12346 Equipment Repair Company 2 10/1/2019 10/1/2024

3 Manufacturer 3 Model 3 $75,000.00 12347 Equipment Repair Company 3 5/1/2017 5/1/2022

4 Manufacturer 4 Model 4 $75,000.00 12345 Equipment Repair Company 1 9/30/2020 9/30/2024

And this is as close as I've been able to get with the formula

=SUMIFS(D2:D5,today,">="&G2:G5,today,"<="&H2:H5)

Any help or advice would be appreciated

Hello!

Please carefully study the recommendations and examples in the article above.

=SUMIFS(D2:D5,G2:G5,">="&TODAY(),H2:H5,"<="&TODAY())

I hope my advice will help you solve your task.

Hello,

Could you please help me with the following. I am attempting to get the average value for a specific date, and a unique reference from another tab. Trying to build IF statement but it keeps failing.

Eg.

The unique reference in a data sheet is B07RVJH98X_IF94837_EU (it can have multiple values depending on the EU country so trying to use the average) for a date 10/10/2022, 20/10/2022 and so on

Hi!

Please clarify your problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.

Hello,

My apologies for not being more specific, but I managed to sort out my issue with this formula "=SUMIFS('REPORT'!$S:$S,'REPORT'!$B:$B,$F4,'REPORT'!$K:$K,Q$3)". However, now I am facing another difficulty. Is it possible to build an IF statement that will bring the latest value for a specific reference "XYZ" if the value is 0 for the current date?

E.g.

In column A - I have a unique reference, in rows B to R I have specific dates (10/12, 20/12, 30/12, 10/01 etc and so on) so I am bringing value for the date and my unique reference from the REPORT sheet. However, what I would like to achieve is - if there is no current value for the 10/01 then bring the latest from e.g. the 30/12 or 20/12.

Very helpful. Just one question. I am working with a data set comprised of void transactions over a year. Each row is a specific void transaction, with columns for date, employee, reason for void, amount, etc. Every time I try to do the 'sumifs' for a date range I get 0. The big difference I noticed between the data in your examples and mine is that in yours every row (representing a transaction) is a different date. I have a lot of transactions on the same day. Just to see what would happen, I changed all the dates in my set to be different from one another. Then the formula works. I'm sure there has to be a work around or something. Thank you

Hi!

The same dates do not affect the calculations. Perhaps your dates were written as text, and then you wrote the normal dates.

Maybe this article will be helpful: Convert text to date and number to date.

Dear all please help how to sum between two dates

Month Rate Per Day Start date End Date

January-23 $225.81 15-01-23 07-02-23

February-23 $250.00

March-23 $225.81

April-23 $233.33

May-23 $225.81

June-23 $233.33

July-23 $225.81

August-23 $225.81

September-23 $233.33

October-23 $225.81

November-23 $233.33

December-23 $225.81

Hi!

We have a special tutorial on this. Please see: How to use Excel SUMIF with dates.

Sir,,

Please help in suming up sales, within specific time in a day. Eg

10 Jan 23

Like

10:00 AM 10 pieces

10:50AM 50 pieces

11: 45AM 20 pieces

I need to count same way sum of sales between 10 AM to 11AM which should come 60 automatically

Hello!

To calculate the sum in a time interval, use the SUMIFS function -

=SUMIFS(B1:B10,A1:A10,">"&TIME(10,0,0),A1:A10,"<"&TIME(11,0,0))

Column A Buy Date | Column B Sell Date | Column C Profit

31/3/2022 | 31/3/2022 | $100

31/5/2022 | 31/5/2022 | $50

31/3/2022 | 10/4/2022 | $20

31/8/2022 | 31/8/2022 | $10

11/4/2022 | 30/4/2022 | $100

Any formula that can do a sum if of profit if Days held is

0 ($160 in this case: row 1, 2, 5)

0-7 days ($0 in this case)

8-14 days ($20 in this case: row 3)

Trying to do this without adding an extra column in that does Col B - Col A

15-22 days ($100 in this case: row 5)

Hello!

To find the sum of a formula result condition, use the SUMPRODUCT function.

For example, 0 days:

=SUMPRODUCT(C1:C5,--((B1:B5-A1:A5)=0))

I hope my advice will help you solve your task.

Thank you sir!

duty shift& time name revenue

Morning 08-02 A 200

Morning 09-03 b

Morning 10-04 A 152

evening 08-02 b

evening 09-03 A 100

evening 10-04 b 3

count formula to get no of staff worked day and night separately (blank cells not worked)

Hi!

Use the SUMIFS formula to find the sum by conditions. Look for the example formulas here: Excel SUMIFS and SUMIF with multiple criteria. I hope I answered your question.

Thanks! That was the fix!

Hi, thanks in advance for any help, i'm struggling to find a solution but I'm sure it's simple!

I'm trying to sum a range if another range contains the current year, year-1 and current year +1 i.e.

Dec 2021 = £300

Jan 2022 = £100

Feb 2022 = £400

Jan 2023 = £200

Output should be 2021 = £300 2022 = £500 2023 = £200

I've tried =SUMIFS(B15:M15,B14:M14,”>=”&DATE(C7,1,1),B14:M14,”<=”&DATE(C7,12,31))

B15:M15 are costs per month. B14:M14 are dates pulled through from another sheet.

C7 is current year ( calculated by the formula =YEAR(A1) where A1 is today's date)

Hello!

Unfortunately, I don't understand what the problem is. The formula calculates the amount for the year from cell C7.

Use the below:

For Previous Year: SUMIFS(16:16,15:15,">="&"01/01/"&C7-1,15:15,"="&"01/01/"&C7,15:15,"="&"01/01/"&C7+1,15:15,"<="&"12/31/"&C7+1)

Prev Year: SUMIFS(16:16,15:15,">="&"01/01/"&C7-1,15:15,"<="&"12/31/"&C7-1)

Current Year: SUMIFS(16:16,15:15,">="&"01/01/"&C7,15:15,"<="&"12/31/"&C7)

Next Year: SUMIFS(16:16,15:15,">="&"01/01/"&C7+1,15:15,"<="&"12/31/"&C7+1)

hi i want unique id based sum "= 5 Year" with one month date range........can you please help me ?

Nikshya ID Date = 5 Year

20286998 04-10-2021 2 4

20286998 04-10-2021 0 0

20285948 04-10-2021 5 6

20285948 04-10-2021 0 0

20363127 04-10-2021 2 2

20363127 04-10-2021 0 0

20363127 04-10-2021 0 0

20363127 04-10-2021 0 0

20162933 05-10-2021 1 2

20162933 05-10-2021 0 0

20162933 05-10-2021 0 0

20162933 05-10-2021 0 0

20162933 05-10-2021 0 0

20162933 05-10-2021 0 0

20522006 05-10-2021 2 2

Hi!

Unfortunately, I do not understand what you are trying to calculate. Describe your problem in more detail. Write an example of the expected result.

I have 50 employees, I need to sum the total labels picked in a time frame of an hour:

example EEID# 1111 between the hours of 10:00 to 11:00 he picked 8 labels

EEID# Total Labels Time

1111 - 3 - 10:01

1111 - 5 - 10:20

Hi!

Have you tried the ways described in this blog post? Use time instead of a date condition. For example

=SUMIFS(B2:B10, C2:C10, ">=10:00", C2:C10, "<=10:59")

Or use the TIME function to specify the time. ">"&TIME(10,0,0)

Hi, can anyone support, please? I need to sumif quantity of units picked in the warehouse by specific user from time 13:00-14:00.

For example, this person "2345" have picked some quantity of units, and I need to filter out how many units this person did per hrs. It's 8hrs working day, and he picked all day, and I need to split by individual hrs. I hope this make sense?Thank you

Hello!

You can use the formulas recommended in the article above. Add the necessary conditions to them.

For example,

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

F1 and G1 - start and end time. H1 - person.

I am trying to have my salary auto update on the last working day of every month any help would be appreciated

Hi!

To determine the last working day of the month, use the WORKDAY function.

The formula below will do the trick for you:

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),-1)