count distinct values in range multiple criteria

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,776
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?
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

ExcelElliott

New Member
Joined
Mar 1, 2018
Messages
41
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

Well-known Member
Joined
Feb 5, 2003
Messages
1,776
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?
 
Last edited:

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,776
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

Well-known Member
Joined
Feb 5, 2003
Messages
1,776
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,732
Messages
5,597,798
Members
414,176
Latest member
LK88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top