Excel Formula to count unique values based on three different criteria

Question

I am trying to get a row by row count of unique invoices in a spreadsheet. I want excel to do this by reading either 1 for unique or zero for duplicate. I have had success with =IF(COUNTIF($C$3:C3,C3)>1,0,1).

This has given me an accurate count based on one specific column, but I have not had any luck advancing this beyond the one column. I would like this formula to be based on three criteria, not just two.

  A		B	C		D		E		F		G
  Vendor ID	Name 1	Invoice Number	Inv Date	Sum Amount	Acctg Date	Unique#
  00001		A	0000001		3/16/2015	5.00		5/11/2016	1
  00010		M	0000001		9/14/2015	10.00		5/24/2016	1	
  00010		M	0000001		9/4/2015	15.00		5/24/2016	0
  00005		K	0000285		4/8/2016	20.00		4/18/2016	1
  000106	O	000042		6/7/2016	30.00		6/21/2016	1
  000107	H	006333		4/5/2016	6.00		4/11/2016	1
  000107	H	006333		4/5/2016	6.00		4/12/2016	1

There are duplicates in all the columns because of how I needed to pull the report. I would like a pull down formula that would give me unique values of A, C, F in a 1,0 format on each row line by comparing each of them against a total combination of each of three columns. Please note vendor M having a duplicate invoice number vs vendor H which has two distinct invoices based on the criteria.

This will be a large drain on resources because of the size of the data. I am looking at around 20-90k lines, but maybe someone can show me a better mousetrap? VBA macro? Match Index? Anyway, onwards to the failures!

Please feel free to explain why they didn't work, or how they could. Also please ignore column locations compared to my example as I was moving things around quite frequently.

=A&C&F then use If(countif('ColumnX')), but this didn't work correctly as I found data that was listed as a repeat when it was actually unique. I think the root problem with doing this was combining the date and general formats into one cell.

=SUMPRODUCT((1/COUNTIFS(E3:E1000,E3:E1000,J3:J1000,J3:J1000,G3:G1000,G3:G1000)))

Multiple versions of AND with IF(CountIF)

Multiple versions of =A&C AND CountIF (Date)

I have also looked at the following questions in SE and found them helpful, but ultimately not what I specifically needed, or I failed at implementation.

Simple Pivot Table to Count Unique Values I tried this unsuccessfully based on unique invoices, need three criteria not just one.

Count unique values in Excel See above.

Excel Formula: Count Unique Values in a Row Based on Corresponding Value in Another Row This looks like it should work, but I tried and failed to correctly adapt to my problem.

Excel - Return Count of Unique Values Based on Two Columns This also should work perfectly with addition of third column. Formula yelled at me and called me names. Mentioned something about can't fix stupid.

Please let me know if any parts of the question are unclear. I did my best to not duplicate and trim the information down. Thanks in advance!

Perhaps adding a few expected results to your table would help clarify what you're seeking here. — XOR LX
1) Do you have many rows? As a newbie+ I might think of something in VBA with nested loops, but it will be slow and resources-consuming. For a few rows (100) might work, but not for big tables. 2) Also, as @XORLX said, do you want to sum up the sum amounts of each combination? — CMArg
As @XORLX implied, without examples of your desired results, I cannot help you. — Ron Rosenfeld
"I tried this unsuccessfully based on unique invoices, need three criteria not just one." Pivot tables can handle three criteria. — CMArg
@XOR LX and Ron Rosenfeld please see edited example along with edited text for clarity. — Jester799

John Bustos John Bustos · Accepted Answer · 2016-08-26T18:21:34

If I am understanding your problem correctly, basically you want column G to check if the current row is a duplicate (based on columns A, C and F) of any rows above it. If it is, return a 0, else return a 1.

If that is what you are looking to achieve, you can do so using the COUNTIFS() function to know if there are any duplicates above the row and then simply check if the count = 0 or is > 0 (=0 means it's unique, >0 means it is a duplicate).

Your formula for column G would look as follows:

G2: 1 (obviously we know it is unique since there are no values above it to be a duplicate of)
G3: =IF(COUNTIFS($A$2:A2,A3,$C$2:C2,C3,$F$2:F2,F3)=0,1,0)

then, drag G3 downwards.

Hope this is what you were looking for.

Excel Formula to count unique values based on three different criteria

1 Answers