Sep 27 2019 03:10 AM
Hi,
I have a worksheet regarding a car collision repair workshop (see attachment).
Table 1
Column B - case number
Column C - car brand
Column D - car model
Column E - start date
Column F - end date
Column G - fuel type (being: petrol, electric or hybrid)
Table 2
Column I - single dates from 13-sep-19 until 1-jan-2019
Column J - amount of cars in the workshop per day
Column K - amount of petrol cars in the workshop
Column L - amount of electric cars in the workshop
Column M - amount of hybrid cars in the workshop
I need formulas for columns J to M. I cannot manage to find the right formula or combination of formulas to do this.
Thank you in advance!
Sep 27 2019 04:17 AM
SolutionSee if this is what you are trying to achieve...
In J10
=COUNTIFS(Data[Brand],"<>",Data[Date S],"<="&I10,Data[Date E],">="&I10)
In K10
=COUNTIFS(Data[Fuel type],K$9,Data[Date S],"<="&I10,Data[Date E],">="&I10)
In L10
=COUNTIFS(Data[Fuel type],L$9,Data[Date S],"<="&I10,Data[Date E],">="&I10)
In M10
=COUNTIFS(Data[Fuel type],M$9,Data[Date S],"<="&I10,Data[Date E],">="&I10)
Sep 27 2019 04:30 AM
@Subodh_Tiwari_sktneer it seems to be working, thanks!
Sep 27 2019 05:05 AM
You're welcome! Glad it worked as desired.
Sep 27 2019 04:17 AM
SolutionSee if this is what you are trying to achieve...
In J10
=COUNTIFS(Data[Brand],"<>",Data[Date S],"<="&I10,Data[Date E],">="&I10)
In K10
=COUNTIFS(Data[Fuel type],K$9,Data[Date S],"<="&I10,Data[Date E],">="&I10)
In L10
=COUNTIFS(Data[Fuel type],L$9,Data[Date S],"<="&I10,Data[Date E],">="&I10)
In M10
=COUNTIFS(Data[Fuel type],M$9,Data[Date S],"<="&I10,Data[Date E],">="&I10)