SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-878863%22%20slang%3D%22en-US%22%3ETable%20with%20start%20date%20%26amp%3B%20end%20date%2C%20count%20cells%20on%20specific%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-878863%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20worksheet%20regarding%20a%20car%20collision%20repair%20workshop%20(see%20attachment).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ETable%201%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EColumn%20B%20-%20case%20number%3C%2FP%3E%3CP%3EColumn%20C%20-%20car%20brand%3C%2FP%3E%3CP%3EColumn%20D%20-%20car%20model%3C%2FP%3E%3CP%3EColumn%20E%20-%20start%20date%3C%2FP%3E%3CP%3EColumn%20F%20-%20end%20date%3C%2FP%3E%3CP%3EColumn%20G%20-%20fuel%20type%20(being%3A%20petrol%2C%20electric%20or%20hybrid)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ETable%202%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EColumn%20I%20-%20single%20dates%20from%2013-sep-19%20until%201-jan-2019%3C%2FP%3E%3CP%3EColumn%20J%20-%20amount%20of%20cars%20in%20the%20workshop%20per%20day%3C%2FP%3E%3CP%3EColumn%20K%20-%20amount%20of%20petrol%20cars%20in%20the%20workshop%3C%2FP%3E%3CP%3EColumn%20L%20-%20amount%20of%20electric%20cars%20in%20the%20workshop%3C%2FP%3E%3CP%3EColumn%20M%20-%20amount%20of%20hybrid%20cars%20in%20the%20workshop%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20formulas%20for%20columns%20J%20to%20M.%20I%20cannot%20manage%20to%20find%20the%20right%20formula%20or%20combination%20of%20formulas%20to%20do%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-878863%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-878970%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20with%20start%20date%20%26amp%3B%20end%20date%2C%20count%20cells%20on%20specific%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-878970%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416239%22%20target%3D%22_blank%22%3E%40brooij_bdr%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20if%20this%20is%20what%20you%20are%20trying%20to%20achieve...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20J10%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCOUNTIFS(Data%5BBrand%5D%2C%22%26lt%3B%26gt%3B%22%2CData%5BDate%20S%5D%2C%22%26lt%3B%3D%22%26amp%3BI10%2CData%5BDate%20E%5D%2C%22%26gt%3B%3D%22%26amp%3BI10)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIn%20K10%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCOUNTIFS(Data%5BFuel%20type%5D%2CK%249%2CData%5BDate%20S%5D%2C%22%26lt%3B%3D%22%26amp%3BI10%2CData%5BDate%20E%5D%2C%22%26gt%3B%3D%22%26amp%3BI10)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIn%20L10%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCOUNTIFS(Data%5BFuel%20type%5D%2CL%249%2CData%5BDate%20S%5D%2C%22%26lt%3B%3D%22%26amp%3BI10%2CData%5BDate%20E%5D%2C%22%26gt%3B%3D%22%26amp%3BI10)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIn%20M10%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCOUNTIFS(Data%5BFuel%20type%5D%2CM%249%2CData%5BDate%20S%5D%2C%22%26lt%3B%3D%22%26amp%3BI10%2CData%5BDate%20E%5D%2C%22%26gt%3B%3D%22%26amp%3BI10)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-878973%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20with%20start%20date%20%26amp%3B%20end%20date%2C%20count%20cells%20on%20specific%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-878973%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3Bit%20seems%20to%20be%20working%2C%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879033%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20with%20start%20date%20%26amp%3B%20end%20date%2C%20count%20cells%20on%20specific%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879033%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416239%22%20target%3D%22_blank%22%3E%40brooij_bdr%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3C%2FLINGO-BODY%3E
brooij_bdr
New 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
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.

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