count distinct values in range multiple criteria

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,004
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top