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 29-Jul-18 39.69
3 30-Jul-18 43.58
4 31-Jul-18 44.90
5 1-Aug-18 47.69
6 2-Aug-18 50.69
7 3-Aug-18 42.15
8 4-Aug-18 48.08
9 5-Aug-18 51.65
10 6-Aug-18 46.63
11 7-Aug-18 46.00
12 8-Aug-18 46.00
13 9-Aug-18 50.48
14 10-Aug-18 50.11
15 11-Aug-18 51.79
16 12-Aug-18 52.00
17 13-Aug-18 56
18
19
20 Ref Cell
21 46.82
22

Here is a zip of the file

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

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
29-Jul-18 39.69
30-Jul-18 43.58
31-Jul-18 44.90
1-Aug-18 47.69
2-Aug-18 50.69
3-Aug-18 42.15
4-Aug-18 48.08
5-Aug-18 51.65
6-Aug-18 46.63
7-Aug-18 46.00
8-Aug-18 46.00
9-Aug-18 50.48
10-Aug-18 50.11
11-Aug-18 51.79
12-Aug-18 52.00
13-Aug-18 48.42
14-Aug-18 42.68
15-Aug-18 48.15
16-Aug-18

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)t-online.de


Regards
Claus B.
--
Windows10
Office 2016

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

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:D$32)/SUMPRODUCT(N(WEEKDAY(A$2:A$32,2)5))

Thanks for all the help :-)

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 :D$32)/SUMPRODUCT((WEEKDAY(A$2:A$32,2)5)*(A2:A320))


Regards
Claus B.
--
Windows10
Office 2016

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 :D$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:D$32)/SUMPRODUCT((WEEKDAY(A$2:A$32,2)5)*(A2:A320))


Regards
Claus B.
--
Windows10
Office 2016

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

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:D$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

Hi Milo,

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

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

WEEKDAY(Range,2) is for week start with Monday.
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:D$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

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