Help please! - Count Unique with 2 criteria

someschmuck

New Member
Joined
Apr 26, 2016
Messages
3
Help! Excel Gurus,

I've been trying to figure this one out for the past few hours, but am completely stumped. Below is my sample data.

TransactionID Date Location
201611609 2016/4/24 A
201611609 2016/4/24 A
201611609 2016/4/24 A
201611611 2016/4/24 B
201611611 2016/4/24 B
201611612 2016/4/24 A
201611612 2016/4/24 A
201611613 2016/4/24 A
201611613 2016/4/24 A
201611614 2016/4/25 A
201611614 2016/4/25 A
201611614 2016/4/25 A

i'm trying to count the number of transactionID's in the data set with the date, and Location as the criteria.
For the date of 2016/4/25, location A.
G2=2016/4/24
G3=A

I'd like the data to return as 3. (201611609, 201611612, and 201611613).

I've tried
=SUM(IF(FREQUENCY(IF($B:$B=$G2,IF($C:$C=$G$3,IF($A:$A<>"",MATCH("~"&$A:$A,$A:$A&"",0))))),ROW($A:$A)-ROW($A$2)+1),1))
to no avail.

Any suggestions would be greatly appreciated.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
TransactionIDDateLocation
20161160924/04/2016A
20161160924/04/2016A
20161160924/04/2016A
20161161124/04/2016B
20161161124/04/2016B
20161161224/04/2016A
20161161224/04/2016A
20161161324/04/2016A
20161161324/04/2016A
20161161425/04/2016A
20161161425/04/2016A
20161161425/04/2016A
LocationA
i'm trying to count the number of transactionID's in the data set with the date, and Location as the criteria.Date24/04/2016
For the date of 2016/4/25, location A.
G2=2016/4/24Count of TransactionID
G3=ATransactionIDTotal
2016116093
I'd like the data to return as 3. (201611609, 201611612, and 201611613).2016116122
2016116132
Grand Total7
a very simple pivot table
gives you the info you want

<colgroup><col><col><col span="7"><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
You have an extra bracket in there, plus $A$2 should be $A$1. Also, since it's a resource intensive formula, you should avoid whole column references. Try...

=SUM(IF(FREQUENCY(IF($B$2:$B$13=$G2,IF($C$2:$C$13=$G$3,IF($A$2:$A$13<>"",MATCH("~"&$A$2:$A$13,$A$2:$A$13&"",0)))),ROW($A$2:$A$13)-ROW($A$2)+1),1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Upvote 0
Can you use a couple of helper columns? It's not a super elegant solution, but you could concatenate your two criteria and then use a simple Countif. This would get the job done, but would have to be manually upkept. Not ideal, but should work.

So...
ABCDEF
AcctNumDateLocationCode=B2&C2Copy/Paste Unique Values from D (Use Remove Duplicates)=COUNTIF(D:D,E2)

<tbody>
</tbody>
 
Last edited:
Upvote 0
TransactionIDDateLocation
20161160924/04/2016A
20161160924/04/2016A
20161160924/04/2016A
20161161124/04/2016B
20161161124/04/2016B
20161161224/04/2016A
20161161224/04/2016A
20161161324/04/2016A
20161161324/04/2016A
20161161425/04/2016A
20161161425/04/2016A
20161161425/04/2016A
LocationA
i'm trying to count the number of transactionID's in the data set with the date, and Location as the criteria.Date24/04/2016
For the date of 2016/4/25, location A.
G2=2016/4/24Count of TransactionID
G3=ATransactionIDTotal
2016116093
I'd like the data to return as 3. (201611609, 201611612, and 201611613).2016116122
2016116132
Grand Total7
a very simple pivot table
gives you the info you want

<tbody>
</tbody>

Thank you oldbrewer. I'd prefer to use a pivot table, but I'm trying to put everything onto 1 table. My company's POS system outputs a report. I'm trying to set it up so all they have to do is copy/paste that report into the existing dataset. Since my coworkers do not use excel at all, I'm trying to simplify it as much as possible for them.
 
Upvote 0
Welcome to the forum.

Try:

=COUNTIFS(B:B,G2,C:C,G3)

Thanks Eric. I used COUNTIFS for another part on my dashboard, but it doesn't give me the total unique counts. Just how many times that particular instance has shown up. Appreciate the response though.
 
Upvote 0
Have you tried the formula I offered you earlier in this thread?
 
Upvote 0
Sorry, I misunderstood the question. Have you tried any of the other suggestions? Try Domenic's formula, and if that doesn't work, let us know and we'll try something else.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top