Countifs with only unique values

%3CLINGO-SUB%20id%3D%22lingo-sub-2440036%22%20slang%3D%22en-US%22%3ECountifs%20with%20only%20unique%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2440036%22%20slang%3D%22en-US%22%3E%3CP%3ENeed%20help%20in%20counting%20unique%20values%20while%20not%20double%20counting.%26nbsp%3B%20I%20know%20how%20to%20count%20unique%20values%20with%20either%20a%20function%20or%20filter.%26nbsp%3B%20I%20don't%20know%20how%20to%20combine%20the%20below%20functions.%26nbsp%3B%20The%20first%20function%20counts%20all%20the%20necessary%20vehicles%20in%20a%20particular%20fleet.%26nbsp%3B%20The%20second%20function%20counts%20all%20the%20vehicles%20in%20a%20fleet%20without%20counting%20any%20vehicle%20listed%20twice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIFS(%24B%2413%3A%24B%24347%2C%22%26lt%3B%26gt%3B%22%26amp%3B%22%22%2C%24F%2413%3A%24F%24347%2CB%242%2C%24H%2413%3A%24H%24347%2C%24A%244)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(IF(FREQUENCY(IF(LEN(I13%3AI200)%26gt%3B0%2CMATCH(I13%3AI200%2CI13%3AI200%2C0)%2C%22%22)%2CIF(LEN(I13%3AI200)%26gt%3B0%2CMATCH(I13%3AI200%2CI13%3AI200%2C0)%2C%22%22))%26gt%3B0%2C1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22692%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22129%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2291%22%3E2017%3C%2FTD%3E%3CTD%20width%3D%22179%22%3E2018%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E2019%3C%2FTD%3E%3CTD%20width%3D%22216%22%3ETOTAL%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E51%3C%2FTD%3E%3CTD%3E29%3C%2FTD%3E%3CTD%3E81%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EGasoline%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E50%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDiesel%3C%2FTD%3E%3CTD%3E39%3C%2FTD%3E%3CTD%3E19%3C%2FTD%3E%3CTD%3E53%3C%2FTD%3E%3CTD%3E111%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETOTAL%3C%2FTD%3E%3CTD%3E51%3C%2FTD%3E%3CTD%3E29%3C%2FTD%3E%3CTD%3E81%3C%2FTD%3E%3CTD%3E161%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%2030%20double%20counts%20in%20the%20161%20total.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2440036%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2440363%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20with%20only%20unique%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2440363%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1075859%22%20target%3D%22_blank%22%3E%40sburke8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20which%20version%20of%20Excel%20you%20are%2C%20365%20or%20not%3F%20And%20perhaps%20you%20could%20submit%20small%20sample%20file%20to%20illustrate%20the%20question.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2440576%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20with%20only%20unique%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2440576%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you%20have%20office%20365%2C%20then%20I%20think%20this%20should%20work.%20But%2C%20your%20data%20layout%20is%20not%20very%20clear%20-%20it%20appears%20to%20me%20that%20you%20have%20some%20some%20indicator%20in%20Column%20B%20that%20should%20not%20be%20blank%2C%20the%20year%20is%20in%20Column%20F%2C%20vehicle%20type%20is%20in%20Column%20H%2C%20and%20the%20vehicle%20ID%20is%20in%20Column%20I%3A%3CBR%20%2F%3E%3CBR%20%2F%3ECountA(Unique(Filter(%24I%2413%3A%24I%24347%2C(%24B%2413%3A%24B%24347%26lt%3B%26gt%3B%22%22)*(%24F%2413%3A%24F%24347%3DB%242)*(%24H%2413%3A%24H%24347%3D%24A4))))%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20excel%20versions%20prior%20to%20365%2C%20but%20have%20the%20countifs%20function%2C%20you%20could%20try%3A%3CBR%20%2F%3E%3DSUMPRODUCT((%24B%2413%3A%24B%24347%26lt%3B%26gt%3B%22%22)*(%24F%2413%3A%24F%24347%3DB%242)*(%24H%2413%3A%24H%24347%3D%24A4)%2FCOUNTIFS(%24B%2413%3A%24B%24347%2C%24B%2413%3A%24B%24347%26amp%3B%22%22%2C%24F%2413%3A%24F%24347%2C%24F%2413%3A%24F%24347%26amp%3B%22%22%2C%24H%2413%3A%24H%24347%2C%24H%2413%3A%24H%24347%26amp%3B%22%22%2C%24I%2413%3A%24I%24347%2C%24I%2413%3A%24I%24347%26amp%3B%22%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.

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.