• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

SUMIFS Formula Referencing Cells Containing Dates

You are here: Home / Excel Formulas / SUMIFS Formula Referencing Cells Containing Dates
SUMIFS Formula Referencing Cells Containing Dates
March 1, 2012 by Mynda Treacy

Formulas containing dates and time in Excel can be frustrating if you don’t understand how they work.

And even if you do they seem to work differently from one formula to another!

A few weeks ago Dave wrote to me as he was having trouble getting a SUMIFS formula to correctly use dates referenced in its criteria.

For example let’s take the data below and say we want to sum the Sale Amount if the sales person is β€˜Brady’, and the dates of the sales are between 1/1/2012 and 2/2/2012.

SUMIFS with dates

I’ve given the columns in my table the following named ranges:

  • Sales Person = salesperson
  • Sale Date = sales_date
  • Sale Amount – sale_amt

I’ve set up my criteria at the top of my worksheet:

SUMIFS with dates

And I’ve given cells B5 and C5 range names:

  • B5 = from_date
  • B6 = to_date

I like to use named ranges as it makes building the formula easier and it’s more intuitive to read and interpret what the formula is doing.

SUMIFS Formula Using Date Criteria

In cell B6 I’ve put my SUMIFS formula:

=SUMIFS(sale_amt,salesperson,B4,sales_date,">="&from_date,sales_date,"<="&to_date)

Notice how the first date criterion is made up of text (surrounded by double quotes) then the ampersand, then a reference to a named range.

That’s because;

  • Excel interprets the text ">=" as >=
  • And the ampersand (&) tells Excel to join the text >= to the next part of the formula

Therefore the criterion ">="&from_date solves to read >=B5.

Likewise the criterion "<="&to_date solves to read <=B6.

Alternatively if you wanted to hard code the date criteria your formula would look like this:

=SUMIFS(sale_amt,salesperson,B4,sales_date,">=1/1/2012",sales_date,"<=2/2/2012")

Thanks for your question Dave.

SUMIFS Formula Referencing Cells Containing Dates

More Excel Dates Posts

Excel Shortcut When Using Dates as Formula Criteria

Excel Shortcut When Using Dates as Formula Criteria

More Sumfis Posts

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel FormulasTag: excel dates, sumfis
Previous Post:Excel Evaluate Formula ToolExcel Evaluate Formula Tool
Next Post:Calculate How Long to Make $1m with NPERCalculate How Long to Make $1m with NPER

Reader Interactions

Comments

  1. Tahsin Γ‡etinkaya

    February 13, 2018 at 4:28 am

    Brilliant explanation.
    Thanks a lot. πŸ™‚

    Reply
  2. Juan

    August 26, 2014 at 5:49 am

    Thank you so much for explaining this formula. I looked in many other websites and it wasn’t half as clear as here … very happy I found it.

    Reply
    • Philip Treacy

      August 26, 2014 at 6:57 am

      Hi Juan,

      Glad that you found it useful πŸ™‚

      Regards

      Phil

      Reply
  3. Buddy

    March 14, 2014 at 8:36 pm

    A B C
    Jan-01-14 Potato $100.00
    Jan-01-14 Onion $225.00
    Jan-02-14 Carrot $300.00
    Jan-02-14 Reddish $100.00
    Jan-03-14 Apple $150.00
    Jan-03-14 Orange $200.00

    I want to sum of a given date (e.g. Jan-02-14)

    Reply
    • Catalin Bombea

      March 15, 2014 at 2:35 am

      Hi Buddy,
      If you put the desired date in E1, and the product in F1, you can use the formula:
      =SUMPRODUCT((A1:A6=E1)*(B1:B6=F1)*(C1:C6))
      Catalin

      Reply
  4. Mohammad Salauddin

    January 20, 2014 at 5:01 am

    Name Pieces Value
    salam 2 5000
    kamal 3 3000
    minto 1 2000
    salam 4 2000
    kamal 3 1000
    minto 5 3999
    salam 3 5000
    kamal 6 2500
    minto 6 1600
    salam 4 1500
    kamal 3 1200
    salam 4 2700
    minto 2 3800
    salam 1 7000
    kamal 2 4100
    salam 3 500
    I want to sum salam & kamal values in one cell. so what formula use?

    Reply
    • Catalin Bombea

      January 20, 2014 at 2:43 pm

      Hi Mohammad,
      You can try this:
      =SUMIF(A2:A17;”salam”;C2:C17)+SUMIF(A2:A17;”kamal”;C2:C17)
      Catalin

      Reply
  5. Tariq

    August 25, 2013 at 5:18 pm

    You are great!

    I repent the time I wasted trying to use the “>” sign with cell reference in criteria to compare a date before visiting your site. Thanks for the tip and trick… πŸ™‚

    Reply
    • Mynda Treacy

      August 25, 2013 at 5:31 pm

      πŸ™‚ You’re welcome, Tariq.

      Reply
  6. Francois

    April 24, 2013 at 12:17 am

    Thank you so much for the explanation of sumifs with dates as criteria.
    Microsoft is really a crappy company…
    – nothing about this is given in the help online (they keep repeating the same simple examples that add nothing to a more complex problem)
    – inconsistent with other functions like ‘If’ where you can write: IF(G39>D51,0,1) and it works very well when G39 and D51 are dates. Excel can handle the comparison of dates directly without having to write =IF(G39&”>”&D51,0,1)
    Excel is been made by lousy programmers who never use it or test it…

    Reply
    • Mynda Treacy

      April 24, 2013 at 8:54 am

      Hi Francois,

      Thanks for sharing your feelings (frustrations) with Excel. I used to get confused with what appears to be inconsistent requirements for date handling too, which is why I wrote this tutorial.

      Kind regards,

      Mynda.

      Reply
  7. Sarel

    March 11, 2013 at 7:42 am

    Dear Mynda

    Thank you so much for the explanation of using logical operators (>, <= etc) in the SUMIFS. I had a particular case where I wanted to count the number of times a customer number occur in a dataset, IF it matches 2 conditions. One of these conditions was that the DATE must occur within 90 days of the other occurence(s). I applied your knowledge shared here, in a COUNTIFS statement, and it worked beautifully. Thank you so much.

    Best regards

    Sarel

    Reply
    • Mynda Treacy

      March 11, 2013 at 12:26 pm

      πŸ™‚ You’re welcome, Sarel. Glad I could help.

      Reply
  8. Omid

    February 10, 2013 at 8:30 am

    Can we write a SUMIF/S criteria that include a calculation. I have a 2 column table on one work sheet (WS1) and another 2 columns on another worksheet (WS2). All of them are formated as number. my goal is to fill up WS2!B column based on other data. For example WS2!B2 value is equal to sum of numbers in WS1! B column which their WS1! A value at the same row meet the following criteria:
    Sin (WS2! $A$2)-Sin (WS1! A*)<0.2
    the result will be shows at WS2! B2. and for WS2! B3 criteria would be:
    Sin (WS2! $A$3)-Sin (WS1! A*)<0.2

    Thanks,

    Reply
    • Carlo Estopia

      February 11, 2013 at 12:33 pm

      Hi Omid,

      Greetings.

      It’s good to have you here. Let’s get down to your concerns.

      First, what do you mean by calculation? If you mean a formula within a SUMIF, then I don’t think you can
      have a formula within a SUMIF. We might as well use SUMPRODUCT.

      Second, when you describe your problem, It seems afterall that you don’t need SUMPRODUCT or a SUMIF.
      What you need is a simple IF to return the value in B column from WS1 to the B column of WS2:

      Assumptions: You are in sheet 2 to write the formula for col B.
      WS1 Data
      —A—- —B–
      1- 12…….10
      2- 02…….20
      3- 03…….30
      4- 04…….40
      WS2Data
      —A—- —B–
      1- 0……..see formula below & copy it down
      2- 02…….
      3- 03…….
      4- 04…….

       =IF((SIN('WS1'!A1)-SIN(A1))<0.2,'WS1'!B1,0) 

      This will return or list as you say the value of B col from WS1 to B of WS2.

      IF the SIN of A of WS1 - SIN of A WS2 < .2 then return it to WS2 B Column Else return ZERO(0) Just in case you really needed a sumproduct it will only need one cell to add a column for example column A in WS2:

      =SUMPRODUCT((A1:A4)*((SIN('WS1'!A1:A4)-SIN(OmidWS2!A1:A4))<.2))
      

      Please do send your file for further clarifications here: HELP DESK.
      We are more than willing to help.

      Please read our very informative blogs:
      SUMPRODUCT
      IF FUNCTIONS
      SUMIF

      Cheers.

      CarloE

      Reply
  9. jobet baylon

    February 5, 2013 at 7:39 pm

    madam,

    Good day!

    Thank you for very much, I really appreciate all the information and help you have done.

    More power to you and God bless,

    Jobet

    Reply
    • Mynda Treacy

      February 5, 2013 at 7:44 pm

      Thank you, Jobet πŸ™‚

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.