Forum Discussion
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.
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&""))
5 Replies
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 - 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.
On which version of Excel you are, 365 or not? And perhaps you could submit small sample file to illustrate the question.
