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