How to build Countifs function with varying weekdays in different months?

[mrxlsx]

Hello all,
I have a file with 3 sheets with Sales data. First sheet is for capturing Sales data, second sheet, with some Weekly Analysis and Monthly Analysis using COUNTIFS, third is charts based on second sheet's formulas.

When I use COUNTIFS function to capture the monthly sales nos, the working days in different months vary from each, obviously. Some months have 20 days, some with 21 days, some with 22/23 days. How to write a correct code for varying working days, because I have to capture only the working days in a month, and in THAT MONTH only, leaving all Sat days and Sun days?

My formula goes like this in cell E3 and I have attached my sample file as well.

=COUNTIFS(Master!$B$2:$B$5000,">="&$D$2-30,Master!$B$2:$B$5000,
"<="&$D$2,Master!$C$2:$C$5000,"="&$D3)

eg: If I want to measure Monthly Sales performance for a salesman, Date of Sales Performance measure will be done on the last WorkingDay/WeekDay of that month, in the month Sep-2013, Performance will be taken on 30-Sep-2013, till the 21 weekdays backwards. i.e. 02-Sep-2013. In the month of Aug or Feb the no of weekdays differs. How to handle this problem? Can somebody help me please?

https://hotfile.com/dl/239331737/986d5d6/Dummy.xlsm.html

Thanks
mrxlsx

 

[Andrew Poulsom]

You don't have any weekend days in your data, so why are you concerned about them?

 

[mrxlsx]

Yeah, you are right, the main concern is the varying count of working days in each month.
How to handle that? I am breaking my head here. Please help me. Thank you.

 

[Andrew Poulsom]

To count the number of entries that are in the month and year of the date in D2:

=COUNTIFS(Master!$B$2:$B$5000,">="&DATE(YEAR($D$2),MONTH($D$2),1),Master!$B$2:$B$5000,"<="&DATE(YEAR($D$2),MONTH($D$2)+1,0),Master!$C$2:$C$5000,"="&$D3)

 

[mrxlsx]

Andrew Poulsom,

Its working fine. I learn a lot from that formula. Thank you so much.

mrxlsx.