Countifs with only unique values

[sburke8]

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.

[#2]

@sburke8 

On which version of Excel you are, 365 or not? And perhaps you could submit small sample file to illustrate the question.

[#3 · Accepted solution]

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

[#4]

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

[#5]

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.

[#6]

Good to hear. You're more than welcome.