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

[alyssakhan]

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.

Any ideas? Thanks in advance

[newdoverman]

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.

Please Login or Register  to view this content.

[Glenn Kennedy]

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

[Glenn Kennedy]

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?

[Larry.LeBlanc@O]

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

[newdoverman]

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.

Please Login or Register  to view this content.

[alyssakhan]

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!

[newdoverman]

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.

[alyssakhan]

Here you go

[newdoverman]

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

[alyssakhan]

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!

[newdoverman]

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.

Please Login or Register  to view this content.