Jun 11 2021 09:03 AM
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.
Jun 11 2021 10:12 AM
On which version of Excel you are, 365 or not? And perhaps you could submit small sample file to illustrate the question.
Jun 11 2021 10:59 AM - edited Jun 11 2021 11:01 AM
SolutionIf 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&""))
Jun 11 2021 11:45 AM
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 |
Jun 11 2021 11:57 AM
Jun 11 2021 10:59 AM - edited Jun 11 2021 11:01 AM
SolutionIf 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&""))