Remember Me?

#11
August 21st 18, 06:54 PM
 Junior Member First recorded activity by ExcelBanter: Apr 2013 Posts: 19
Count Weekdays and Weekends

Ok sorry couple last problems.

I need this equation to work in Next Cells E,F,G
Keeps coming up with wrong answer

=SUMPRODUCT(--(WEEKDAY(\$A\$2:\$A\$32,2)<=1),D\$2\$32)/SUMPRODUCT(N(WEEKDAY(\$A\$2:\$A\$32,2)<=1))

This is for Monday and Tuesday. Seems to work for rest of days
Column A is dates starting with 29-Jul-2018 (Sunday)

Data is

L H A
56 83 65
57 79 66
55 87 68
61 85 72
57 91 74
65 87 75
62 94 77
64 87 76
60 82 70
56 87 69
57 83 69
57 87 72
62 88 75
60 90 76
61 89 75
55 88 71
58 85 70
61 87 71
59 87 72
65 87 74
58 78 69
51 73 62

This is in Column A
=IF(D24="",0,MAX(\$A\$2:A23)+1)

Thanks

#12
August 21st 18, 08:09 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,693
Count Weekdays and Weekends separate & < a given cell.

Hi Milo,

Am Tue, 21 Aug 2018 18:54:03 +0100 schrieb Milo Bloom:

=SUMPRODUCT(--(WEEKDAY(\$A\$2:\$A\$32,2)<=1),D\$2\$32)/SUMPRODUCT(N(WEEKDAY(\$A\$2:\$A\$32,2)<=1))

When you use <=1 you only count and sum the values for Monday.
1 =Monday
2=Tuesday
3 = Wednesday
..
..
..

Try:
=SUMPRODUCT(--(WEEKDAY(\$A\$2:\$A\$32,2)<3),D\$2\$32)/SUMPRODUCT(N(WEEKDAY(\$A\$2:\$A\$32,2)<3))
That will calculate the average for values in column D for Monday and
Tuesday.

Regards
Claus B.
--
Windows10
Office 2016
#13
September 7th 18, 06:49 PM
 Junior Member First recorded activity by ExcelBanter: Apr 2013 Posts: 19
Count days greater than a fixed number

a2:a32 are dates I.E. 1-sep-18 to 30-sep-18

b2:b32 are numbers I input.

I want to count # of weekdays greater than cell B37

Thanks in advance for any help! :-)

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post TLAngelo Excel Discussion (Misc queries) 0 July 10th 06 06:49 PM TLAngelo Excel Discussion (Misc queries) 7 May 12th 06 05:31 PM edwardpestian Excel Worksheet Functions 4 May 7th 06 09:13 PM [email protected] Charts and Charting in Excel 2 August 17th 05 02:09 PM hanauer Excel Discussion (Misc queries) 2 January 8th 05 02:13 AM

All times are GMT +1. The time now is 12:35 PM.