Function to sum multiple columns according to date

[hassanm]

Hi,

I need to be able to analyse number of reports coming in every month & year and look at on time completion (Sheet3). What I would like is a function that looks at the 'Completion Date' column in Sheet2 (Column I, Q, Y, AG) and sum them together depending on the date e.g. Jan-2017. And also need to look at 'Forecast Date' column in Sheet2 (Column H, P, X, AF) and compare the report on time % for that month of year.

I have tried the COUNTIFS but this did not work with the way I did it.

Thanks

[rcm]

Take a look at the formulas. Hoping this is what you requested

[rcm]

Hoping this can help you (disregard the previous , no file included)

[leelnich]

Hi all- this approach uses MULTI-CELL array formulas for monthly forecast- and completion-date breakdowns. Select C2:C25 and paste this in the formula bar:

Please Login or Register  to view this content.

Next, press CTRL+SHIFT+ENTER to confirm array status. Finally, while C2:C25 is still selected, use the drag handle to copy column C to column D.
Now paste this single-cell array formula in E2, press CTRL+SHIFT+ENTER to confirm, then copy down (drag handle):

Please Login or Register  to view this content.

Note that column E compares actual forecast- and completion-dates, rather than assuming counted entries are associated.

Named ranges: Data = Sheet2!$C$4:$AH$17, Headers = Sheet2!$C$3:$AH$3

[hassanm]

Hi,

Thanks for this. It works really well.

I noticed that what I asked for does not take into account individual projects forecast and completion date but instead looks at the holistic view. Looking at Row 12 on Sheet3, the completion date is greater than the forecast date.

For example, if I wanted to look at Project 1 what I would like to do is compare the forecast data e.g. H4 to I4 and if I4 was greater than H4 than this would be a late report. I'd like to do this with all projects and each type of report too (column H vs. I, column P vs Q....) Then using this information look at the completion rate of each month and feed this into the functions that you have already provided me with.

Hope that makes sense. Thanks

[AliGW]

@ rcm - Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[hassanm]

Hi

I've looked at the reports coming in and the reports produced on time. See 'Sheet2' columns AT:BB. I need to be able to translate this into each month. I'm guessing the funcitons for the completion dates would stay the same but the report on time function would need to adjusted somehow. Can I have some help on this?

Regards,

Mufeed

[leelnich]

Hi Mufeed- This ARRAY FORMULA* from post#4 already compares each individual forecast date with its associated completion date (the value in the next cell). If a specific forecast date is greater than or equal to its specific completion date, it's counted. The total # of reports due that month AND COMPLETED ON TIME is then divided by the total # of reports due that month. Do you need more?:

Please Login or Register  to view this content.

In English: If any reports are due this month (IF(C2,), then for any cell in a "Forecast Date" column (IF(Headers=C$1), if its value is a date that falls within this month (IFERROR(IF(DATE(YEAR(Data),MONTH(Data),1)=$B2), divide it by the date in the next cell over(Data/OFFSET(Data,0,1)). If the result is >= 1, then the report was completed on time, so mark it TRUE. If the result < 1 or if the next cell is blank, mark it FALSE. Now add up all the TRUES (SUM(--...) and divide by the number of reports due (.../C2). If no reports are due this month, leave it blank (,"")).

*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

[hassanm]

Hi leelnich,

Thank you for explaining the function.

I have tried to use the following equation in another spreadsheet with all the data but it doesn't give me the right results. It gives me the same results as the attachment you posed in #4

Please Login or Register  to view this content.

See Column J&K in Sheet2

Do I have to adjust part of it? Does Headers and Data need to be changed?

Similarly for;

Please Login or Register  to view this content.

See Column L in Sheet2.

Would it be possible to look at column H and see where I am going wrong, what I would like to do is look at the completed reports column for Jan-16 and then look at column BJ inSheet1 to see if it is on time and if it then count is as 1. If not, then produce a 0.

Thanks.

[leelnich]

Something like this?

Month	PFR due	on time	POR due	on time	PDR due	on time	SC due	on time	Total due	on time		Total due (Formula)	Total on time (Formula)	% on time	Total Completed (Formula)
Jan-16	0	0	2	1	0	0	0	0	2	1		2	1	50%	1
Feb-16	0	0	6	2	2	1	0	0	8	3		8	3	38%	6
Mar-16	0	0	3	1	2	2	0	0	5	3		5	3	60%	6
Apr-16	1	1	8	4	0	0	1	1	10	6		10	6	60%	7

This is the "due" formula for the PFR report. It's basically the same as yours, but for some reason you were reading Completion Dates instead of Forecast Dates! Formulas for other reports read different columns HERE - INDEX(Data,,6).

Please Login or Register  to view this content.

The "ontime" formula for PFR uses SUMPRODUCT in a similar fashion, but has to also compare column to column, which COUNTIFS can't.

Please Login or Register  to view this content.

And yes, you just needed to re-define the named ranges in the "new" workbook (Name Manager on the Formula tab):
Headers =Sheet1!$G$3:$AP$3
Data =Sheet1!$G$4:$AP$165

[hassanm]

Hi Leelnich,

That is great. Exactly what I needed.

After going through the spreadsheet, I have some follow on questions if that's OK. If I want a rolling function where I want a separate tab which allows me to identify all the different reports that are due (Forecast date) within the next 30 days, 60 days etc. and it keeps rolling as the days go on. I have used the NOW() function in S9 to give me the date.

Also, a function to identify all reports with the 'To Checker' being less than x days (an adjustable no. of days) away from the Forecast Date. The reason why is I want to ensure enough time is given for the checking and approving stage. So for example, IF column K's date (To Checker) is greater than x days away from column L (Forecast Date), then it's OK. But if it less than x days then I would like it to be flagged up in a separate list or another way to identify it.

Also if I want to look at reports between certain dates i.e. between Jan-2017 to Oct-2017, then would it be a case of using the

Please Login or Register  to view this content.

What does the red section do?

I also realise that inputting a date in the wrong format may mess up the function, therefore is there a way to ensure no errors occur when inputting dates?

Thanks for your help so far. It really helps.

[AliGW]

Let's suppose that B4 contains today's date - 18/10/2017. This:

=EOMONTH($B4,0)

tells Excel to return the last date in the month in B4, so 31/10/2017.

This:

=EOMONTH($B4,1)

would tell Excel to return the last date in the month after B4, so 30/11/2017, and this:

=EOMONTH($B4,-1)

would return the last date of the month before B4, i.e. 30/09/2017.