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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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>
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,801
Office Version
  1. 365
Platform
  1. Windows
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!
 

BBEKKS

New Member
Joined
Oct 23, 2013
Messages
2
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:

someschmuck

New Member
Joined
Apr 26, 2016
Messages
3
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.
 

someschmuck

New Member
Joined
Apr 26, 2016
Messages
3
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.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,801
Office Version
  1. 365
Platform
  1. Windows
Have you tried the formula I offered you earlier in this thread?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,597
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.
 

Forum statistics

Threads
1,186,105
Messages
5,955,862
Members
438,222
Latest member
nalij_bond

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
Top