• 544K Members
• 4,082 Online
• 648K Conversations
SOLVED

New Contributor

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

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.

3 Replies
Highlighted
Solution

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

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

Highlighted

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

@Subodh_Tiwari_sktneer it seems to be working, thanks!

Highlighted

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

You're welcome! Glad it worked as desired.

Related Conversations
Date format in Stream
Dave Pyett in Microsoft Stream Forum on
3 Replies
Counting Days
Tim Hunter in SQL Server on
2 Replies
How to count multiple values in a cell
Ugarte335 in Excel on
7 Replies
Count until
MBelshaw in Excel on
1 Replies
Pivot table
gabriellerocha in Excel on
5 Replies