• 412K Members
• 7,700 Online
• 468K 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
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)``

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

@Subodh_Tiwari_sktneer it seems to be working, thanks!

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

You're welcome! Glad it worked as desired.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies