SOLVED

Countifs with only unique values

New Contributor

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

 

 201720182019TOTAL
 512981 
Gasoline12102850
Diesel391953111
TOTAL512981161

 

There are 30 double counts in the 161 total.

5 Replies

@sburke8 

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

best response confirmed by allyreckerman (Microsoft)
Solution

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&""))

offfice 365 ProPlus

 

 Vehicle

  201720182019
  512981
 Gasoline121028
 Diesel391953
 LOGGERS TOTAL512981
 F-450191221
 F-550321760
 TOTAL512981
     
Installation DateModel YearModelGas or DieselVIN
24-May-192017F-550Diesel######
24-May-192017F-550Diesel###9jdjje
5-Sep-192017F-450Dieseldedexex
13-Sep-192017F-550Gasolineedednndkended
13-Sep-192017F-550Gasolinececedcdec
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.
Good to hear. You're more than welcome.