+ Reply to Thread
Results 1 to 6 of 6

SUMIF or SUMIFS to find text and date between from items in a table

  1. #1
    Registered User
    Join Date
    01-29-2014
    Location
    Bend, Oregon
    MS-Off Ver
    Excel 2010
    Posts
    27

    SUMIF or SUMIFS to find text and date between from items in a table

    Hello,
    I am in need of help figuring out a SUMIF or SUMIFS formula which will assist me clean up some weekly data. I am envisioning a SUMIF formula which looks at the client name in column A in a table and then it will search through the long list of data for all entries for that specific client on another sheet in column "A", for instance. It needs to take into consideration only the encounters which happened between the dates in the table for that client listed in column B & C. The sum will be the column next to the column with each client's name which has a procedure date in between the date criteria’s from the table. I have attached an example to better illustrate.
    Thanks
    Bend SUMIF Example.xlsx

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIF or SUMIFS to find text and date between from items in a table

    Try sumifS

    =SUMIFS('Master Sheet'!I:I,'Master Sheet'!B:B,A2,'Master Sheet'!C:C,">="&B2,'Master Sheet'!C:C,"<="&C2)

  3. #3
    Registered User
    Join Date
    01-29-2014
    Location
    Bend, Oregon
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: SUMIF or SUMIFS to find text and date between from items in a table

    That works in the example I gave, but when I put it in with my larger data set it does not work correctly. I reset them to match the correct columns, but I think the problem is that the names are not actually client 1 or client 2. They are last then first name separated by a comma. Any thoughts? I just got 0's again.

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIF or SUMIFS to find text and date between from items in a table

    How the names are written shouldn't matter, as long as they're written the same in both column B and A2.

    Could also be that some of your dates aren't really dates, just text strings that look like dates.

    Can you post a more realistic example?

  5. #5
    Registered User
    Join Date
    01-29-2014
    Location
    Bend, Oregon
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: SUMIF or SUMIFS to find text and date between from items in a table

    Well the report which spits out the data for my master list puts the name as: Smith , Bobby and I have been typing names into the table the same but with only one space after the comma: Smith, Bobby. Not one space after the last name and two spaces after the comma. Is there a better way for it to not be so picky?

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMIF or SUMIFS to find text and date between from items in a table

    Nope, Vlookup is picky when it comes to exact matches.
    And 'closest match' is dicey to say the least with TEXT values (it's really meant for number lookups)

    I'd recommend doing a find/replace on the data from the report
    Find What: space comma space
    Replace With: comma space
    Replace All

+ 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/SUMIFS not working using Date fields as criteria
    By waynees in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2013, 09:24 AM
  2. Pivot table to find number of items moved given in an amount of time
    By dsoeder in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-20-2013, 01:16 AM
  3. [SOLVED] Need to find how many times items from a particular date are duplicated throughout sheet
    By mo4391 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-29-2013, 06:16 PM
  4. [SOLVED] sumifs date and text combination
    By ktmjamal in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-14-2013, 12:12 AM
  5. [SOLVED] SumIf / SumIfs with multiple Contains Text criteria
    By theweirdone in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-13-2012, 05:22 AM

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