Excel Countifs that count duplicate entries once

Question

I'm trying to create a countifs formula that counts duplicate entries once. See below example data set. With "Vegetable" being cell A1.

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.

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

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

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).

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.

Thanks in advance.

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

You need to create a user-defined function in VBA for this. I don't think it can be done with standard functions, unless some new ones have been added after the initial Excel 2016 release. — JamesFaix
Alternately, you may be able to use a pivot table on another sheet, which should be configured to have one row per value of the field in question. Then you can use COUNT to count the rows on that sheet. — JamesFaix

Mister 832 Mister 832 · Accepted Answer · 2016-12-09T10:38:44

Try this Array formula (Keep in mind to enter the formula using Ctrl+Shift+Enter!!)

=SUM(IF(($A$2:$A$21="Potato")*($B$2:$B$21<>"Mashed")*($C$2:$C$21="1 (ASS) xxx")*($D$2:$D$21<>"N/A")*($D$2:$D$21<>""),1/COUNTIFS($A$2:$A$21,"Potato",$B$2:$B$21,"<>"&"Mashed",$C$2:$C$21,"1 (ASS) xxx",$D$2:$D$21,"<>"&"",$D$2:$D$21,$D$2:$D$21),0))

formula with Wildcard search:

=SUM(IF(($A$2:$A$21="Potato")*($B$2:$B$21<>"Mashed")*(ISNUMBER(SEARCH("*1 (ASS)*",$C$2:$C$21,1)))*($D$2:$D$21<>"N/A")*($D$2:$D$21<>""),1/COUNTIFS($A$2:$A$21,"Potato",$B$2:$B$21,"<>"&"Mashed",$C$2:$C$21,"*1 (ASS)*",$D$2:$D$21,"<>"&"",$D$2:$D$21,$D$2:$D$21),0))

formula with Wildcard search and search-text in Cell F2:

=SUM(IF(($A$2:$A$21="Potato")*($B$2:$B$21<>"Mashed")*(ISNUMBER(SEARCH("*"&$F$2&"*",$C$2:$C$21,1)))*($D$2:$D$21<>"N/A")*($D$2:$D$21<>""),1/COUNTIFS($A$2:$A$21,"Potato",$B$2:$B$21,"<>"&"Mashed",$C$2:$C$21,"*"&$F$2&"*",$D$2:$D$21,"<>"&"",$D$2:$D$21,$D$2:$D$21),0))

Excel Countifs that count duplicate entries once

1 Answers