Counting Unique values in a column based on the values in other columns

[BillP]

I'm trying to count the number of unique transaction ID, based on values in three other columns.


Table:


Transaction Type Start End

6778 1 4 10

6779 1 5 11

6780 1 5 11

6780 1 5 11

6781 0 5 11

6782 0 5 11

6783 1 5 12

6783 1 5 12

6784 1 6 12


There are 7 unique values in the Transaction column. There are 5 unique transactions where type = 1, 3 unique transactions where type = 1 AND start = 5, and 2 unique transactions where type = 1 AND start = 5 AND End = 11


How can I make a formula to count the unique transactions where I have the three conditions?

 

[Robapottamus]

Hi There


This might be a work around, but is relatively formula and method to follow (for me anyway):


I would firstly create a helper field in the next available comlumn (I assume column D, titled "Yes/No Flag") using the following formula:

=if(and(b1=1,c1=5,d1=11),"Yes","No).

Then you can use a pivot table, with "Yes/No Flag" as the filter,and can count for each unique transaction ID.

Hope that helps.

 

[Robapottamus]

sorry, rushing, got my letters mixed up in the formula. Replace with:


=if(and(a1=1,b1=5,c1=11),"Yes","No).