Excel Countifs that count duplicate entries once
<p>I'm trying to create a countifs formula that counts duplicate entries once. See below example data set. With "Vegetable" being cell A1.</p>

<p>In it I am trying to count KPIs impacted (column D) of potato (column A), that are not mashed (column B), with "1 (ASS)" occurring in Data 1 (column C). Column D has both blank cells and "N/A" which cannot be counted. Column C displays data from a multi-select list, and is comma separated.</p>

<p>I have entered the formula to count the total number of KPIs in cell H2 on my workbook (see image). Answer is 3. The formula I used was </p>

<p>=COUNTIFS($A$3:$A$22,"Potato",$B$3:$B$22,"&lt;>"&amp;"Mashed",$C$3:$C$22,"<em>"&amp;$F$3&amp;"</em>",$D$3:$D$22,"&lt;>"&amp;"",$D$3:$D$22,"&lt;>"&amp;"N/A")</p>

<p>However I can't create anything that will allow me to count the number of unique KPIs impacted. By unique I mean count the first instance / count duplicate entries once. The answer should be 2 in H3 (again, see image).</p>

<p>In my data set I can create a new column if necessary, but if there is way to answer this using just a formula that is my preferred solution. </p>

<p>Thanks in advance.</p>

<pre><code>Vegtable    Type    Data 1                                     KPIs
Potato      Peeled  1 (ASS) xxx,11 (ToW) xxx                   1 ABC
Potato      Boiled  1 (ASS) xxx                                1 ABC
Potato      Mashed  1 (ASS) xxx                                3 ABC
Potato      Boiled  1 (ASS) xxx                                N/A
Potato      Boiled  1 (ASS) xxx                                N/A
Potato      Boiled  1 (ASS) xxx 
Potato      Peeled  1 (ASS) xxx                                7 ABC
Potato      Peeled  2 (ASS) xxx,8 (SGA) xxx                    8 ABC
Potato      Peeled  2 (ASS) xxx                                9 ABC
Potato      Peeled  2 (ASS) xxx                                10 ABC
Potato      Peeled  8 (SGA) xxx 
Carrot      Peeled  8 (SGA) xxx 
Carrot      Peeled  8 (SGA) xxx                                1 ABC
Carrot      Peeled  8 (SGA) xxx                                2 ABC
Carrot      Boiled  8 (SGA) xxx                                3 ABC
Carrot      Boiled  8 (SGA) xxx 
Carrot      Boiled  1 (ASS) xxx,8 (SGA) xxx,11 (ToW) xxx    
Carrot      Boiled  11 (ToW) xxx                               4 ABC
Carrot      Boiled  11 (ToW) xxx                               5 ABC
Carrot      Boiled  1 (ASS) xxx,11 (ToW) xxx                   6 ABC
</code></pre>

<p><a href="https://i.stack.imgur.com/sHiWL.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sHiWL.jpg" alt="enter image description here"></a></p>