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