COUNTIFS Problem with varying workdays in the months

[rkspeaks]

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 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)


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


Thanks

RK

 

[Sara]

Hi RK


Due to problems at my end I can't open your file.


Have you tried the NETWORKDAYS formula?

http://chandoo.org/excel-formulas/networkdays.shtml

 

[Hui]

RK


Your current Month End Score formula is in error (Analysis!E3)


The $D$2-30 when applied to 28 Feb returns Jan 29th instead of Feb 1


I recommend that you use the EOMonth() function instead of hard coding the Month start offset values

eg: EOMONTH($D$2,-1)+1 which returns Feb 1


So that E8 becomes:

=COUNTIFS(Master!$B$2:$B$5000,">="&(EOMONTH(D2,-1)+1),Master!$B$2:$B$5000,"<="&EOMONTH(D2,0),Master!$C$2:$C$5000,"="&$D3)

 

[rkspeaks]

Sorry Hui,


I have given an incorrect explanation of my problem. This is the actual file uploaded.

I have sales data relating to all the days including Sat days, Sun days.

But I have to take the count of only the Weekdays' sales out of them. I have done something at my end, but it is not giving the required result. My requirement is finding the weekly, monthly, quarterly performance of Salesmen using the COUNTIFS function. Please correct my workings and help me find out the correct formula code. Thank you.


Hints:

1. Weekly Sales Performance test will be done on any weekday for the previous 5 weekdays.


2. Monthly Sales Performance test will be done on any last weekday of any month.


3. Quarterly Sales Performance test will be done on any last weekday of the month for the previous three months.


https://hotfile.com/dl/239604100/7b8b75c/Dummy.xlsm.html


Thanks a lot for everybody.

RK