count distinct values in range multiple criteria

[ajm]

I am trying to count distinct vehicle numbers in a fleet schedule that have come in for Maintenance between certain dates.

I have used the formula below to count the number of distinct vehicles:

=SUMPRODUCT(IF((Order_Date<=$O$4)*(Order_Date>=$E$4), 1/COUNTIFS(Order_Date, "<="&O4, Order_Date, ">="&E4, Fleet, Fleet), 0))

How do I add he job type in the formula?

 

[ExcelElliott]

Apologies for the silly response, but have you tried
SUMPRODUCT(IF((Job_Type=Criteria)*(Order_Date<=$O$4)*(Order_Date>=$E$4), 1/COUNTIFS(Order_Date, "<="&O4, Order_Date, ">="&E4, Job_Type, Criteria,Fleet, Fleet), 0))

That would be my best guess without seeing the data.

Hope it helps.

 

[ajm]

Apologies for the silly response, but have you tried
SUMPRODUCT(IF((Job_Type=Criteria)*(Order_Date<=$O$4)*(Order_Date>=$E$4), 1/COUNTIFS(Order_Date, "<="&O4, Order_Date, ">="&E4, Job_Type, Criteria,Fleet, Fleet), 0))

That would be my best guess without seeing the data.

Hope it helps.

nothing silly in the response.

I had initially tried:

SUMPRODUCT(IF((Order_Date<=$O$4)*(Order_Date>=$E$4)*(Job="Planned Maintenance"), 1/COUNTIFS(Job,"Planned Maintenance",Order_Date, "<="&O10, Order_Date, ">="&E10, Fleet, Fleet), 0))

entered as an array gives me Div/0 error which means I have a naughty naught in there somewhere.

so, question is, where do I put in the iferror to capture the zero?

 

[ajm]

I found a solution at:
https://www.get-digital-help.com/co...-meet-multiple-criteria-in-excel/#twocriteria

weird though as it puts the criteria before the range in the numerator.

={SUM(IF(("Tyres"=Job)*(Order_Date>EOMONTH(Analysis!$E$4,-1))*(Order_Date<=Analysis!$O$4),1/COUNTIFS(Job,"Tyres",Fleet,Fleet,Order_Date,">"&EOMONTH(Analysis!$E$4,-1),Order_Date,"<="&Analysis!$O$4)),0)}

notice "Tyres" = Range and its a sum not sumproduct

 

[ajm]

so, I have got it working. however, its resource intensive. is there a better way to get distinct counts of data when there are other criteria to consider? I am trying to not use a pivot table as I am creating this report for someone else and trying to keep it as simple as possible.

 

[ExcelElliott]

Don't know if this solution is much better but might be worth a shot:

https://exceljet.net/formula/count-unique-numeric-values-with-criteria