New Contributor

# Countifs with only unique values

Need help in counting unique values while not double counting.  I know how to count unique values with either a function or filter.  I don't know how to combine the below functions.  The first function counts all the necessary vehicles in a particular fleet.  The second function counts all the vehicles in a fleet without counting any vehicle listed twice.

=COUNTIFS(\$B\$13:\$B\$347,"<>"&"",\$F\$13:\$F\$347,B\$2,\$H\$13:\$H\$347,\$A\$4)

=SUM(IF(FREQUENCY(IF(LEN(I13:I200)>0,MATCH(I13:I200,I13:I200,0),""),IF(LEN(I13:I200)>0,MATCH(I13:I200,I13:I200,0),""))>0,1))

 2017 2018 2019 TOTAL 51 29 81 Gasoline 12 10 28 50 Diesel 39 19 53 111 TOTAL 51 29 81 161

There are 30 double counts in the 161 total.

5 Replies

# Re: Countifs with only unique values

On which version of Excel you are, 365 or not? And perhaps you could submit small sample file to illustrate the question.

# Re: Countifs with only unique values

If you have office 365, then I think this should work. But, your data layout is not very clear - it appears to me that you have some some indicator in Column B that should not be blank, the year is in Column F, vehicle type is in Column H, and the vehicle ID is in Column I:

CountA(Unique(Filter(\$I\$13:\$I\$347,(\$B\$13:\$B\$347<>"")*(\$F\$13:\$F\$347=B\$2)*(\$H\$13:\$H\$347=\$A4))))

For excel versions prior to 365, but have the countifs function, you could try:
=SUMPRODUCT((\$B\$13:\$B\$347<>"")*(\$F\$13:\$F\$347=B\$2)*(\$H\$13:\$H\$347=\$A4)/COUNTIFS(\$B\$13:\$B\$347,\$B\$13:\$B\$347&"",\$F\$13:\$F\$347,\$F\$13:\$F\$347&"",\$H\$13:\$H\$347,\$H\$13:\$H\$347&"",\$I\$13:\$I\$347,\$I\$13:\$I\$347&""))

# Re: Countifs with only unique values

offfice 365 ProPlus

Vehicle

 2017 2018 2019 51 29 81 Gasoline 12 10 28 Diesel 39 19 53 LOGGERS TOTAL 51 29 81 F-450 19 12 21 F-550 32 17 60 TOTAL 51 29 81 Installation Date Model Year Model Gas or Diesel VIN 24-May-19 2017 F-550 Diesel ###### 24-May-19 2017 F-550 Diesel ###9jdjje 5-Sep-19 2017 F-450 Diesel dedexex 13-Sep-19 2017 F-550 Gasoline edednndkended 13-Sep-19 2017 F-550 Gasoline cecedcdec

# Re: Countifs with only unique values

CountA(Unique(Filter(\$I\$13:\$I\$347,(\$B\$13:\$B\$347<>"")*(\$F\$13:\$F\$347=B\$2)*(\$H\$13:\$H\$347=\$A4))))

Worked perfect! Thank you.

# Re: Countifs with only unique values

Good to hear. You're more than welcome.