+ Reply to Thread
Results 1 to 4 of 4

Need help on Sumif and If formula in regards to Date

  1. #1
    Registered User
    Join Date
    04-04-2017
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    1

    Post Need help on Sumif and If formula in regards to Date

    Hello Team,

    I have these three columns in my excel. I need to find how many claims are pending with Status Code 17 for each month.

    A B C
    Initial Work Date Amount Status Code
    01/13/17 $128.00 17
    02/01/17 $59.00 17
    01/18/17 $128.00 01
    01/18/17 $59.00 17
    02/20/17 $59.00 17
    03/01/17 $59.00 17
    01/18/17 $59.00 01
    03/05/17 $59.00 17
    01/18/17 $672.00 17

    If Column A2:A10 is equal to or before 01/31/17, then what is the amount which is pending.(17 Status Code)
    If Column A2: A10 is after 01/31/17 but is equal to or before 02/28/17, then what is the amount which is pending (17 Status Code)
    and the same way If Column A2: A10 is after 02/28/17 but equal to or before 03/31/17, then what is the amount which is pending (17 Status Code)

    Please help with your suggestions.

    Regards
    Abhi.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Need help on Sumif and If formula in regards to Date

    Use a Pivot Table. See the example.
    Attached Files Attached Files
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Need help on Sumif and If formula in regards to Date

    Better example
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Need help on Sumif and If formula in regards to Date

    Welcome to the forum.

    If you don't want a pivot table, you can use SUMIFS.

    Put your dates in one column - let's say D2, D3 and D4 for 31st Jan, 28th Feb and 31st Mar.
    Then in E2 put this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In E3 put this and drag down to E4:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Hope that helps (as an alternative - the pivot table is more flexible).
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] SUMIF Formula where date is less than or equal to today's date
    By Iain170 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-22-2017, 10:14 AM
  2. How to input date range for SUMIF formula?
    By Viero7 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-24-2014, 04:57 PM
  3. SUMIF by Date - SUMIF by Date = #VALUE when linked worksheet not open
    By alexhartley in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-14-2013, 03:56 AM
  4. Help with sumif formula - year to date
    By MCunningham in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-04-2013, 08:48 PM
  5. Formula using sumif depending on date in two cells
    By enhydra in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-19-2012, 04:27 AM
  6. [SOLVED] SUMIF(By Date) - SUMIF(By Date)= #Value Error From Linked Data
    By Dragunov in forum Excel General
    Replies: 2
    Last Post: 05-17-2012, 11:22 AM
  7. Date Range in SumIf Formula?
    By burgeon in forum Excel General
    Replies: 2
    Last Post: 02-22-2005, 05:56 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1