SOLVED

Table with start date & end date, count cells on specific date

Copper Contributor

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!

3 Replies
best response confirmed by brooij_bdr (Copper Contributor)
Solution

@brooij_bdr 

See 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)

 

@brooij_bdr 

You're welcome! Glad it worked as desired.

1 best response

Accepted Solutions
best response confirmed by brooij_bdr (Copper Contributor)
Solution

@brooij_bdr 

See 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)

 

View solution in original post