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

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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

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

#### 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