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 |