# Sum ifs - Based on Matching Date (exact date not range)

1. ## Sum ifs - Based on Matching Date (exact date not range)

I am trying to count the number of records If cell B (text) is unique and cell C (date) are on the same date.

For example:
(B) ------------------------- (C)
Dollar General (ARC 20)-------2/20/2014
Dollar General (ARC 20)-------2/20/2014
Dollar General (ARC 20)-------2/26/2014
Dollar General (ARC 20)-------2/20/2014
Dollar General (ARC 20)-------2/20/2014
Dollar General (ARC 20)-------3/31/2014
DG 31-Pack ------------------2/20/2014

Based on the above data, I should have a count of 4. So basically I want to count if B and C match.

I have part of the formula working, but it is only doing the first part, if Column B matchs - =SUM(IF(FREQUENCY(MATCH(B2:B307,B2:B307,0),MATCH(B2:B1000,B2:B307,0))>0,1))

I need something for if C also matches.

2. ## Re: Sum ifs - Based on Matching Date (exact date not range)

This gives a count of the value in column B and the date in column C combination that match in columns B and C. I suspect that there is more to it than just this.

This will give a count of 4 in the example given.

Formula:
`Please Login or Register  to view this content.`

3. ## Re: Sum ifs - Based on Matching Date (exact date not range)

I suspect so, too. What if there are two sets of pairs that match: A-B occurring twice and C-D occurring twice

4. ## Re: Sum ifs - Based on Matching Date (exact date not range)

I suspect so, too. What if there are two sets of pairs that match: A-B occurring twice and C-D occurring twice.

Also, does your equation work if the first row does not contain the most commonly occurring pair of strings?

5. ## Re: Sum ifs - Based on Matching Date (exact date not range)

Can you concatenate A and B and count the number of unique occurrences that result?

6. ## Re: Sum ifs - Based on Matching Date (exact date not range)

The COUNTIFS matches the criteria used. In this case the value in column B is matched with the date in column C and that combination is counted in the range defined.

If you enter this formula in column D and copy down, it will give a count for each unique combination as one is found.

Formula:
`Please Login or Register  to view this content.`

7. ## Re: Sum ifs - Based on Matching Date (exact date not range)

Based on the above, it was counting both items in column B:
Cordova & Tradewinds - Pensacola, FL 1/3/2014 2.00
Cordova & Tradewinds - Pensacola, FL 1/3/2014 2.00

So the data shows two deals with the same same name and on the same date, so the count should be 1. But should also only show a total of 1, as I will later sum this amount to get the total.

I am going to try concatenating the columns, and getting a unique count that way.

Thanks!

8. ## Re: Sum ifs - Based on Matching Date (exact date not range)

Make sure that the entries that you cite are "exactly" the same and not just look the same. A leading or trailing space will not be obvious but makes the entries different as far as Excel is concerned.

Can you post the data? I would like to see why you got the result you did because you shouldn't have.

Here you go

10. ## Re: Sum ifs - Based on Matching Date (exact date not range)

The only thing that I can think of is that the formula wasn't correctly converted to be used with your data. I copied the formula from my previous message, pasted it into the worksheet, changed the cell ranges and it worked as it should...take a look at column F

11. ## Re: Sum ifs - Based on Matching Date (exact date not range)

Thanks for your help! The only thing it is still counting the total number of deals, so if I were to total the amount, it would be incorrect.

Some background: I am trying to getting a count of all the transactions closed on the same day, if they have the same portfolio name and closed on the same day, it is considered 1 transaction (not the 6 individual records)

Here are the results from the excel sheet, in red: what I want to see is 1, a total of 1. I just want the unique count.

Capview Restaurant Portfolio - 6 Pack 3/28/2014 6.00 6
Capview Restaurant Portfolio - 6 Pack 3/28/2014 6.00
Capview Restaurant Portfolio - 6 Pack 3/28/2014 6.00
Capview Restaurant Portfolio - 6 Pack 3/28/2014 6.00
Capview Restaurant Portfolio - 6 Pack 3/28/2014 6.00
Capview Restaurant Portfolio - 6 Pack 3/28/2014 6.00
Cordova & Tradewinds - Pensacola, FL 1/3/2014 2.00 2
Cordova & Tradewinds - Pensacola, FL 1/3/2014 2.00

Do you think concatenating both cells and getting a unique count would be best?

Thanks again!

12. ## Re: Sum ifs - Based on Matching Date (exact date not range)

Change the formula in F1 to this and copy down. This will give 1 for each set of matches instead of the count of the matches.

Formula:
`Please Login or Register  to view this content.`

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