Counting Days per Month in a Time Range (certain week days only)

[excel_noob_]

Hey everyone,

My question is the following:
I have a schedule for a set of connections A-C provided to you as per below screenshot and I would need to know is how often each connection is served in a respective calender month.



In case the “Day of Week” column entries aren’t self explanatory: for example - connection A is only served on the weekdays Mondays, Tuesdays, Saturdays & Sundays.



I am certain that the sumproduct function can be used to solve this problem – and by means of using workarounds I was already able to find out how to count the total number of frequencies in the timeframe. Unfortunately, I can’t figure out the missing AND criterion that would allow me to distinguish the number of frequencies per calendar month. Do you guys have a solution for this one?



Sincerely,

Finn

 

[excel_noob_]

Sorry forgot to attach the image. Point being: each connection is operative within a time range on specific days of weeks. The question is: how do I get the desired output? I am guessing with the sumproduct function?

 

[jasonb75]

Welcome to Mr Excel, excel_noob_ aka Finn

I don't see a screenshot in your post, although screenshots are not of much use anyway. It would be better if you post a capture using XL2BB (green button on the reply box toolbar, then follow the instructions). That will provide a sample that we can copy and paste into excel to test formulas rather than having to manually retype your data.

 

[jasonb75]

This should do what you need, I thought that NETWORKDAYS.INTL would be a better way to do it, but this proved more accurate.

Book2
	A	B	C	D	E	F	G	H
2	Conn	Start	End	Weekday		May	June	July
3	a	25/05/2020	02/06/2020	12…67		4	2	0
4	b	04/06/2020	27/07/2020	12.4.67		0	20	19
5	c	12/06/2020	24/06/2020	.2.4.67		0	7	0
Sheet5

Cell Formulas
Range	Formula
F3:H5	F3	=SUMPRODUCT(ISNUMBER(SEARCH(WEEKDAY(ROW(INDEX($B:$B,$B3):INDEX($B:$B,$C3)),2),$D3))*(TEXT(ROW(INDEX($B:$B,$B3):INDEX($B:$B,$C3)),"mmmm")=F$2))