multiple month columns - count different type of work orders by month

O

oanarxn

New Member
Joined
Mar 20, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi,



I am trying to build a report and I struggle with counting the work orders on a month by month basis in a single Table Visualisation because each type of work has a different month column depending on it's status and source of work.



The result I want to achieve is below.



JanuaryFebruaryMarchAprilMayJuneJuly
Programme Works (Source of Work = Programme , PROGRAMME Planned Month for completion) 6
Programme Works Adhoc (Source of Work = Programme Adhoc , Planned Month for completion) 1
Completed Programme Works (Source of Work = Programme Adhoc + Programme , Status = Completed, Actual Completion Date) 11
Forecast Works (Status = Scheduled, Planned Month for completion) 42


Data source:



Source of WorkPROGRAMME Planned month for work to startPROGRAMME Planned Month for completionPlanned month for work to startPlanned Month for completionStatus of Proposed workActual Start DateActual Completion Date
ProgrammeMarchMarchAprilAprilScheduled
ProgrammeMarchMarchMarchMarchScheduled
ProgrammeMarchMarchMarchMarchScheduled
ProgrammeMarchMarchAprilAprilScheduled
ProgrammeMarchMarchMarchMarchScheduled
ProgrammeMarchMarchMarchMarchCompleted01/03/202115/03/2021
Programme Adhoc FebruaryFebruaryCompleted15/02/202118/02/2021
Adhoc FebruaryFebruaryCompleted25/02/202126/02/2021
Adhoc FebruaryMarCompleted16/03/202116/03/2021
Adhoc FebruaryMarCompleted15/03/202115/03/2021
Adhoc FebruaryFebruaryCompleted16/02/202119/02/2021




How could I do this in a single table visualisation?



Thank you
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
This is pretty ugly but see if this will work for you. It won't work if you have more than a few options ie currently only Programme and Adhoc.
I haven't attempted the forecast row because I am not clear on the criteria.
Also Programme Adhoc seems an inconsistency so the formulas are not picking it up.


20210321 Programme Countifs.xlsx
ABCDEFGHI
4Lookup nameJanuaryFebruaryMarchAprilMayJuneJuly
5ProgrammeProgramme Works (Source of Work = Programme , PROGRAMME Planned Month for completion)0060000
6AdhocProgramme Works Adhoc (Source of Work = Programme Adhoc , Planned Month for completion)0000000
7Completed Programme Works (Source of Work = Programme Adhoc + Programme , Status = Completed, Actual Completion Date)0230000
8Forecast Works (Status = Scheduled, Planned Month for completion)0000000
9
10
11
12Source of WorkPROGRAMME Planned month for work to startPROGRAMME Planned Month for completionPlanned month for work to startPlanned Month for completionStatus of Proposed workActual Start DateActual Completion Date
13ProgrammeMarchMarchAprilAprilScheduled
14ProgrammeMarchMarchMarchMarchScheduled
15ProgrammeMarchMarchMarchMarchScheduled
16ProgrammeMarchMarchAprilAprilScheduled
17ProgrammeMarchMarchMarchMarchScheduled
18ProgrammeMarchMarchMarchMarchCompleted1/03/202115/03/2021
19Programme AdhocFebruaryFebruaryCompleted15/02/202118/02/2021
20AdhocFebruaryFebruaryCompleted25/02/202126/02/2021
21AdhocFebruaryMarCompleted16/03/202116/03/2021
22AdhocFebruaryMarCompleted15/03/202115/03/2021
23AdhocFebruaryFebruaryCompleted16/02/202119/02/2021
24
25
Sample
Cell Formulas
RangeFormula
C5:I6,C8:I8C5=COUNTIFS($D$13:$D$23,C$4,$B$13:$B$23,$A5)
C7:I7C7=COUNTIFS($B$13:$B$23,"Programme", $G$13:$G$23,"Completed", $I$13:$I$23,">=" & DATEVALUE( "01-"&C$4&"-2021"), $I$13:$I$23,"<=" & EOMONTH(DATEVALUE( "01-"&C$4&"-2021"),0)) +COUNTIFS($B$13:$B$23,"Adhoc", $G$13:$G$23,"Completed", $I$13:$I$23,">=" & DATEVALUE( "01-"&C$4&"-2021"), $I$13:$I$23,"<=" & EOMONTH(DATEVALUE( "01-"&C$4&"-2021"),0))
 
Upvote 0
Solution
Alex Blakenburg said:
This is pretty ugly but see if this will work for you. It won't work if you have more than a few options ie currently only Programme and Adhoc.
I haven't attempted the forecast row because I am not clear on the criteria.
Also Programme Adhoc seems an inconsistency so the formulas are not picking it up.


20210321 Programme Countifs.xlsx
ABCDEFGHI
4Lookup nameJanuaryFebruaryMarchAprilMayJuneJuly
5ProgrammeProgramme Works (Source of Work = Programme , PROGRAMME Planned Month for completion)0060000
6AdhocProgramme Works Adhoc (Source of Work = Programme Adhoc , Planned Month for completion)0000000
7Completed Programme Works (Source of Work = Programme Adhoc + Programme , Status = Completed, Actual Completion Date)0230000
8Forecast Works (Status = Scheduled, Planned Month for completion)0000000
9
10
11
12Source of WorkPROGRAMME Planned month for work to startPROGRAMME Planned Month for completionPlanned month for work to startPlanned Month for completionStatus of Proposed workActual Start DateActual Completion Date
13ProgrammeMarchMarchAprilAprilScheduled
14ProgrammeMarchMarchMarchMarchScheduled
15ProgrammeMarchMarchMarchMarchScheduled
16ProgrammeMarchMarchAprilAprilScheduled
17ProgrammeMarchMarchMarchMarchScheduled
18ProgrammeMarchMarchMarchMarchCompleted1/03/202115/03/2021
19Programme AdhocFebruaryFebruaryCompleted15/02/202118/02/2021
20AdhocFebruaryFebruaryCompleted25/02/202126/02/2021
21AdhocFebruaryMarCompleted16/03/202116/03/2021
22AdhocFebruaryMarCompleted15/03/202115/03/2021
23AdhocFebruaryFebruaryCompleted16/02/202119/02/2021
24
25
Sample
Cell Formulas
RangeFormula
C5:I6,C8:I8C5=COUNTIFS($D$13:$D$23,C$4,$B$13:$B$23,$A5)
C7:I7C7=COUNTIFS($B$13:$B$23,"Programme", $G$13:$G$23,"Completed", $I$13:$I$23,">=" & DATEVALUE( "01-"&C$4&"-2021"), $I$13:$I$23,"<=" & EOMONTH(DATEVALUE( "01-"&C$4&"-2021"),0)) +COUNTIFS($B$13:$B$23,"Adhoc", $G$13:$G$23,"Completed", $I$13:$I$23,">=" & DATEVALUE( "01-"&C$4&"-2021"), $I$13:$I$23,"<=" & EOMONTH(DATEVALUE( "01-"&C$4&"-2021"),0))
Click to expand...
It does help! Thank you very much. :)
 
Upvote 0
Great, thanks for letting me know. Unless you want to keep the call open a while, can you click on the "Mark as solution" button on my initial response.
(there should be a button on the right hand side or the response box - if you need more information on this it can be found here)

PS: If the "Completed Programme" summary line is always going to sum ALL "Source of Work", you can simplify that formula by getting rid of the second CountIfs and removing the "$G$13:$G$23,"Completed"," from the 1st CounIifs.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Power Query - Units per Month
Replies
2
Views
300
therealjdj
T
R
  • Question
Monthly and Weekly Distinct Count
Replies
6
Views
1K
pjmorris
P
S
  • Question
Running Total by Month & by Product
Replies
0
Views
351
srehman
S
R
  • Locked
  • Question
Monthly and Weekly Distinct Count
Replies
1
Views
410
Fluff
Fluff
C
  • Question
Power BI: Double noob Question: Baseline & Time Line
Replies
4
Views
580
Worf
Worf

Forum statistics

Threads
1,215,379
Messages
6,124,605
Members
449,174
Latest member
ExcelfromGermany

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