Fill Countifs formular across coloum with varing dates as the critical range

A

amtoyin

New Member
Joined
Apr 7, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Good Day,

I am new on the forum and would like help with an excel sheet I am working on.

I have various jobs done on a daily basis and each day is represented on a sheet, with the whole month as a workbook.
At the end of the month I have to create a summary of the activities carried out each day.
I have a several formulas on the summary sheet.
I need a way to quickly fill in the formula for the rest of the column if I have the formula on the first column.
I have attached a sample for January 2020.

Alternatively,

If their is a quicker way to achieve the same result, I am open to ideas.


Jan 2020.xlsx
ABCDEFGHI
1PREVENTINE MAINTENANCE WORK ORDER
2DomainCodeLabelTypeSiteBuildingStatus UploadedComment
3HVAC228238Task PM FCU(3M) on 18FCUFLATPMECT1T1Closed Yes
4HVAC228234Task PM FCU(3M) on 18FCUT06BPMECT1T1Closed Yes
5HVAC228230Task PM FCU(3M) on 18FCUT2FPMECT1T1Closed Yes
6HVAC228226Task PM FCU(3M) on 18FCUT2EPMECT1T1Closed Yes
7HVAC228222Task PM FCU(3M) on 18FCUT2DPMECT1T1Closed Yes
8HVAC228218Task PM FCU(3M) on 18FCUT2CPMECT1T1Closed Yes
9HVAC228214Task PM FCU(3M) on 18FCUT2BPMECT1T1Closed Yes
10HVAC228210Task PM FCU(3M) on 18FCUT2APMECT1T1Closed Yes
11HVAC228206Task PM FCU(3M) on 18FCUT01PMECT1T1Closed Yes
12HVAC228202Task PM FCU(3M) on 18FCUT06APMECT1T1Closed Yes
13HVAC228198Task PM FCU(3M) on 18FCUT05PMECT1T1Closed Yes
14HVAC228194Task PM FCU(3M) on 18FCUT04PMECT1T1Closed Yes
15HVAC228190Task PM FCU(3M) on 18FCUT03PMECT1T1Closed Yes
16PLUMB227329DAILY CHECK LIST/SERVICE TOILET( DAY ...PMECT1T1Closed Yes
17CIVIL223379Task PM WC CUBICLES on 14ALLRMWCUBICL...PMECT1T1Closed Yes
18CIVIL223358Task PM WC CUBICLES on 16ALLRMWCUBIC...PMECT1T1Closed Yes
19CIVIL209839DAILY CHECK ON DOORS - 01/01/2020PMECT1T1Closed No
20LIGHTS209384DAILY MAINTENANCE OF LIGHTING FITTING...PMECT1T1Closed No
21LIGHTS209014DAILY MAINTENANCE OF LIGHTING FITTING...PMECT1T1Closed No
22PLUMB208462DS ON WATER TREATMENT PLANT - 01/01/2020PMECT1T1Closed Yes
23PLUMB207809DAILY CHECK OF TECHNICAL EQUIP(PLUMB)...PMECT1T1Closed Yes
24ELECT206905OPERATION CHECK LIST FOR MDB TECHNICA...PMECT1T1Closed Yes
25MLTDOM206540DAILY MDB CHECKLIST NIGHT - 01/01/2020PMECT1T1Closed Yes
26HVAC205118Task PM-PRECISION A/C(m) on PAU10FDC04PMECT1T1Closed Yes
27HVAC205117Task PM-PRECISION A/C(m) on PAU10FDC03PMECT1T1Closed Yes
28HVAC205116Task PM-PRECISION A/C(m) on PAU10FDC02PMECT1T1Closed Yes
29CIVIL205115Task PM CEILING on 01ALLRMACCOUSTICEILPMECT1T1Closed Yes
30CIVIL205114Task PM CEILING on ACCOUSTICEILINGPMECT1T1Closed No
31HVAC205113Task PM-PRECISION A/C(m) on PAU10FDC01PMECT1T1Closed Yes
32HS205112Task PM-SMOKE DETECTORS on 17ALLRMSMKDTPMECT1T1Closed No
33ELECT205108Task PM-ELECT BOARDS on 10F-C01-LPPMECT1T1Closed Yes
34ELECT205107Task PM-ELECT BOARDS on RF-C01-LPPMECT1T1Closed Yes
35PLUMB205099Task PM-PLUMBING FIXTURES on 01-FPLUM...PMECT1T1Closed Yes
36PLUMB205098Task PM-PLUMBING FIXTURES on GF-FPLUM...PMECT1T1Closed Yes
37PLUMB205097Task PM-PLUMBING FIXTURES on BA-FPLUM...PMECT1T1Closed Yes
38HVAC205085Task PM-PRECISION A/C(m) on PAU3FDC04PMECT1T1Closed Yes
39CIVIL205084Task PM CEILING on BAALLRMACCOUSTICEILPMECT1T1Closed Yes
40CIVIL161127CHECK ON KITCHEN DOORS AND CABINETS -...PMECT1T1Closed No
41
Jan 1


Jan 2020.xlsx
ABCDEFGHI
1PREVENTINE MAINTENANCE WORK ORDER
2DomainCodeLabelTypeSiteBuildingStatusUploadedComment
3HVAC228799Task PM FCU(3M) on 08FCUT06BPMECT1T1ClosedYes
4HVAC228795Task PM FCU(3M) on 08FCUT04PMECT1T1ClosedYes
5HVAC228791Task PM FCU(3M) on 08FCUT2CPMECT1T1ClosedYes
6HVAC228787Task PM FCU(3M) on 08FCUT2BPMECT1T1ClosedYes
7HVAC228783Task PM FCU(3M) on 08FCUT2EPMECT1T1ClosedYes
8HVAC228779Task PM FCU(3M) on 08FCUT2FPMECT1T1ClosedYes
9HVAC228775Task PM FCU(3M) on 08FCUT03PMECT1T1ClosedYes
10HVAC228771Task PM FCU(3M) on 08FCUT05PMECT1T1ClosedYes
11HVAC228767Task PM FCU(3M) on 08FCUT06APMECT1T1ClosedYes
12HVAC228763Task PM FCU(3M) on 08FCUT1APMECT1T1ClosedYes
13HVAC228759Task PM FCU(3M) on 08FCUT2APMECT1T1ClosedYes
14HVAC228755Task PM FCU(3M) on 08FCUT1BPMECT1T1ClosedYes
15HVAC228250Task PM FCU(3M) on 08FCUT2DPMECT1T1ClosedYes
16PLUMB227330DAILY CHECK LIST/SERVICE TOILET( DAY ...PMECT1T1ClosedYes
17CIVIL223392Task PM WC CUBICLES on 01ALLRMWCUBICL...PMECT1T1ClosedYes
18LIGHTS209417DAILY MAINTENANCE OF LIGHTING FITTING...PMECT1T1ClosedYes
19LIGHTS209045DAILY MAINTENANCE OF LIGHTING FITTING...PMECT1T1ClosedYes
20PLUMB208463DS ON WATER TREATMENT PLANT - 02/01/2020PMECT1T1ClosedYes
21PLUMB207810DAILY CHECK OF TECHNICAL EQUIP(PLUMB)...PMECT1T1ClosedYes
22ELECT206906OPERATION CHECK LIST FOR MDB TECHNICA...PMECT1T1ClosedYes
23MLTDOM206541DAILY MDB CHECKLIST NIGHT - 02/01/2020PMECT1T1ClosedYes
24ELECT205160Task PM - THERMOGRAPHY INSPECTION on TR3PMECT1T1ClosedYes
25ELECT205159Task PM - THERMOGRAPHY INSPECTION on TR2PMECT1T1ClosedYes
26ELECT205158Task PM - THERMOGRAPHY INSPECTION on TR1PMECT1T1ClosedYes
27CIVIL205152Task PM CEILING on 02ALLRMACCOUSTICEILPMECT1T1ClosedYes
28HS205151Task PM-SMOKE DETECTORS on 15ALLRMSMKDTPMECT1T1ClosedYes
29HS205150Task PM-SMOKE DETECTORS on 16ALLRMSMKDTPMECT1T1ClosedYes
30ELECT205149Task PM ON AUTOMATIC EXIT GATE on EXI...PMECT1T1ClosedNo
31LIGHTS205146Task PM EMERGENCY LIGHT FITTINGS(M) o...PMECT1T1ClosedNo
32LIGHTS205145Task PM EMERGENCY LIGHT FITTINGS(M) o...PMECT1T1ClosedNo
33ELECT205144Task PM-ELECT BOARDS on 12F-C01-LPPMECT1T1ClosedYes
34ELECT205143Task PM-ELECT BOARDS on 11F-C01-LPPMECT1T1ClosedYes
35HVAC205140Task PM-PRECISION A/C(m) on PAU10FMTC04PMECT1T1ClosedYes
36PLUMB205139Task PM-PLUMBING FIXTURES on 05-FPLUM...PMECT1T1ClosedYes
37PLUMB205138Task PM-PLUMBING FIXTURES on 04-FPLUM...PMECT1T1ClosedYes
38PLUMB205137Task PM-PLUMBING FIXTURES on 03-FPLUM...PMECT1T1ClosedYes
39PLUMB205136Task PM-PLUMBING FIXTURES on 02-FPLUM...PMECT1T1ClosedYes
40CIVIL205123Task METALLICIELING on 02ALLRMMETALLI...PMECT1T1ClosedYes
41CIVIL205122Task METALLICIELING on 01ALLRMMETALLI...PMECT1T1ClosedYes
42HVAC205121Task PM-PRECISION A/C(m) on PAU10FMTC03PMECT1T1ClosedYes
43HVAC205120Task PM-PRECISION A/C(m) on PAU10FMTC02PMECT1T1ClosedYes
44HVAC205119Task PM-PRECISION A/C(m) on PAU10FMTC01PMECT1T1ClosedYes
45
46
47OTHER MAINTENANCE WORK ORDERS
48DomainCodeLabelTypeSiteBuildingStatusUploadedComment
49HVAC303915NOISY AC VENTTIECT1T1ClosedYes
50HVAC303913NOISY AC VENTTIECT1T1ClosedYes
51HVAC303912A/C NOT COOLINGTIECT1T1ClosedYes
52PLUMB303911WATER RUNNING NON STOPTIECT1T1ClosedYes
53ELECT303908LIGHT ISSUETIECT1T1ClosedNo
54HVAC303907AC TOO COLDTIECT1T1ClosedNo
55CIVIL303899WATER LEAKAGE ON GF SICKBAY TOILETCMECT1T1ClosedNo
56CIVIL301093FAULTY TISSUE HOLDERCMECT1T1ClosedNo
57CIVIL206180DAILY WASTE DISPOSAL - 02/01/2020DSECT1T1ClosedNo
Jan 2


01-Jan​
02-Jan​
03-Jan​
04-Jan​
05-Jan​
06-Jan​
07-Jan​
08-Jan​
09-Jan​
10-Jan​
11-Jan​
12-Jan​
13-Jan​
14-Jan​
15-Jan​
16-Jan​
17-Jan​
18-Jan​
19-Jan​
20-Jan​
21-Jan​
22-Jan​
23-Jan​
24-Jan​
25-Jan​
26-Jan​
27-Jan​
28-Jan​
29-Jan​
30-Jan​
31-Jan​
PMTOTAL
TOTAL
38​
42​
80​
Uploaded
32​
39​
71​
Not-Uploaded
6​
3​
9​
CM & Others
TOTAL
0​
9​
9​
Uploaded
0​
4​
4​
Not-Uploaded
0​
5​
5​
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
There is no ideal way to do it, the only function that will be able to select the correct sheet based on the date in the column header is INDIRECT, which is not a good function to rely on. It is volatile, which means that is is recalculated more often than necessary and will likely make your workbook run a lot slower, often to the point where it becomes unusable with delays every time you try to edit or add data to the sheet.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,920
Messages
6,122,267
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top