Home 
Search 
Today's Posts 
#1




Count Weekdays and Weekends separate & < a given cell.
I have got this far,
{=COUNT(IF(WEEKDAY(A$2:A$32,2)<6,B$2:B$32))} Col A is dates: July 29 to Aug 28 Col B is a number usually between 18 to 55 Ref Cell is average of Weekdays Col B I want to count Number of weekdays = than Ref Cell Same for weekends Thanks in Advance Milo I have got this far, Cell G2 {=COUNT(IF(WEEKDAY(A$2:A$32,2)<6,B$2:B$32))} Col A is dates: July 29 to Aug 28 Col B is a number usually between 18 to 55 Ref Cell is average of Weekdays Col B I want to count Number of weekdays = than Ref Cell Same for weekends Thanks in Advance Milo A B C D E 1 Date Usage 2 29Jul18 39.69 3 30Jul18 43.58 4 31Jul18 44.90 5 1Aug18 47.69 6 2Aug18 50.69 7 3Aug18 42.15 8 4Aug18 48.08 9 5Aug18 51.65 10 6Aug18 46.63 11 7Aug18 46.00 12 8Aug18 46.00 13 9Aug18 50.48 14 10Aug18 50.11 15 11Aug18 51.79 16 12Aug18 52.00 17 13Aug18 56 18 19 20 Ref Cell 21 46.82 22 
#2




File
Here is a zip of the file

#3




Count Weekdays and Weekends separate & < a given cell.
Hi Milo,
Am Wed, 15 Aug 2018 23:15:39 +0100 schrieb Milo Bloom: Ref Cell is average of Weekdays Col B for average of weekdays try: =SUMPRODUCT((WEEKDAY(A2:A17,2)<6),B2:B17)/SUMPRODUCT(N(WEEKDAY(A2:A17,2)<6)) Regards Claus B.  Windows10 Office 2016 
#4




Count Weekdays and Weekends
Ok Thanks that worked (sort of)
The sheet goes on for 32 lines header and 31 days. the formula will error if =IF(D20="","",A19+1) is placed in Column A Column A can be blank Column D is Data field If you fill in the date fields (column A) to 31 days then it averages the data by 31. It comes out ok at the end of the month. =SUMPRODUCT((WEEKDAY($A$2:$A$32,2)<6),$D$2:$D$32)/SUMPRODUCT(N(WEEKDAY($A$2:$A$32,2)<6)) The formula works fine except if the date column contains a formula Date Usage 29Jul18 39.69 30Jul18 43.58 31Jul18 44.90 1Aug18 47.69 2Aug18 50.69 3Aug18 42.15 4Aug18 48.08 5Aug18 51.65 6Aug18 46.63 7Aug18 46.00 8Aug18 46.00 9Aug18 50.48 10Aug18 50.11 11Aug18 51.79 12Aug18 52.00 13Aug18 48.42 14Aug18 42.68 15Aug18 48.15 16Aug18 
#5




Count Weekdays and Weekends separate & < a given cell.
Hi Milo,
Am Sat, 18 Aug 2018 18:01:38 +0100 schrieb Milo Bloom: Ok Thanks that worked (sort of) The sheet goes on for 32 lines header and 31 days. the formula will error if =IF(D20="","",A19+1) is placed in Column A here it works fine with formula and with blank cells. I guess anything is wrong with your formats. Can you send me an example workbook to claus_busch(at)tonline.de Regards Claus B.  Windows10 Office 2016 
#6




Count Weekdays and Weekends separate & < a given cell.
Hi Milo,
Am Sat, 18 Aug 2018 18:01:38 +0100 schrieb Milo Bloom: The sheet goes on for 32 lines header and 31 days. the formula will error if =IF(D20="","",A19+1) is placed in Column A WEEKDAY requires a date. If you have an empty string in a cell it errors out. But your formula is anyhow not really reliable. Beneath the empty cell you get a wrong date. Try in A3: =IF(D3="";0;MAX($A$2:A2)+1) Copy the formula down and then go to Options = Advanced = Display options for this worksheet and deactivate "Show a zero in cells that have zero value". Regards Claus B.  Windows10 Office 2016 
#7




Ok Thanks that worked :)
Now one last problem, If I change the date to 5 for weekend for some reason it does not work properly. Seems again to be counting all dates, including blanks. I did change the display options and that worked. =SUMPRODUCT((WEEKDAY(A$2:A$32,2)5),D$2$32)/SUMPRODUCT(N(WEEKDAY(A$2:A$32,2)5)) Thanks for all the help :) 
#8




Count Weekdays and Weekends separate & < a given cell.
Hi Milo,
Am Mon, 20 Aug 2018 18:17:29 +0100 schrieb Milo Bloom: Now one last problem, If I change the date to 5 for weekend for some reason it does not work properly. Seems again to be counting all dates, including blanks. I did change the display options and that worked. if you have a zero in column A the weekday is Saturday and will be counted. Change the formula for weekends: =SUMPRODUCT((WEEKDAY(A$2:A$32,2)5)*(A2:A320)*D$2 $32)/SUMPRODUCT((WEEKDAY(A$2:A$32,2)5)*(A2:A320)) Regards Claus B.  Windows10 Office 2016 
#9




Count Weekdays and Weekends separate & < a given cell.
Hi again,
Am Mon, 20 Aug 2018 20:15:49 +0200 schrieb Claus Busch: if you have a zero in column A the weekday is Saturday and will be counted. Change the formula for weekends: =SUMPRODUCT((WEEKDAY(A$2:A$32,2)5)*(A2:A320)*D$2 $32)/SUMPRODUCT((WEEKDAY(A$2:A$32,2)5)*(A2:A320)) for the sum you don't need to check in column A is greater than 0 This will be enough: =SUMPRODUCT((WEEKDAY(A$2:A$32,2)5)*D$2$32)/SUMPRODUCT((WEEKDAY(A$2:A$32,2)5)*(A2:A320)) Regards Claus B.  Windows10 Office 2016 
#10




Count Weekday and Weekend
Woo Hoo we have Victory!!
Thanks so much for the help :) =SUMPRODUCT((WEEKDAY(A$2:A$32,2)5)*(A2:A32)*D$2:$ D32)/SUMPRODUCT((WEEKDAY(A$2:A$32,2)5)*(A2:A32)) 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
WEEKENDS VS. WEEKDAYS  Excel Discussion (Misc queries)  
WEEKENDS VS. WEEKDAYS  Excel Discussion (Misc queries)  
IF statement with Weekends vs. weekdays  Excel Worksheet Functions  
chart only weekdays when data set includes weekends?  Charts and Charting in Excel  
Recognizing weekends and changing to weekdays in Excel  Excel Discussion (Misc queries) 