Count in a variable range

[Guest]

I am trying to count the number of "W"s entered into a row (e.g. row 8).
Row 7 has the days of each month (i.e. 1 thro' 28, 30, 31 as appropriate)
each number is entered in two merged cells (identifies a.m & p.m. in the rows
below)
Cell E2 contains a date
Using a formula, I need to count the "W"s in a range between a (first cell)
variable column row 8 and GW8 (formula will be copied down)
The variable first cell is dependent upon the date (E2) thus using
=ADDRESS(8,MATCH(DAY(E2),A7:AP7,0),4) will result in the correct cell for
the beginning of the range.
=COUNTIF(AM8:GW8,"W") works perfectly, however when the two are incorporated
thus
=COUNTIF(ADDRESS(8,MATCH(DAY(E2),A7:AP7,0),4):GW8,"W") I am told "The
formula I typed contains an error".
Any ideas what am I doing wrong?

 

[Bob Phillips]

Try

=COUNTIF(OFFSET(AM8,0,0,1,MATCH(DAY(E2),A7:AP7,0)),"W")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thank you Bob, works OK if a "W" is entered in cells AM8, AN8, AO8 onwards.
But AM8 was used as an example to represent the first cell of the variable
range. Tried changing AM8 to L8 (1 is in row 7) but doesn't work! The ADDRESS
function demonstrates that when the date in E2 changes the "AM8" cell
reference also changes. The purpose of the variable cell reference is to
ignore any "W"s that appear in the row (8) to the left of the variable cell
reference. (e.g. if date in E2 is 10/09/2007 and "W" appears in a cell (row
8) below any number (row 7) between 1 and 9 - COUNTIF ignores it.
Would appreciate if you could spare time to revisit.
Thanks

 

[Bob Phillips]

Is this what you mean?

=COUNTIF(INDEX(8:8,MATCH(DAY(E2),A7:AP7,0)):IV8,"W")

I have to admit to being a tad confused as if you put the day numbers in A7
on, the furthest you get to is AE7, so how can there be W in AM8 etc. if the
Ws align with the day numbers.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Bob, thank you but this does not identify the range to count the "W"s. it
still includes in the count any "W"s entered to the left of the column
identified by the DAY(E2)function.
Apologies if I confused you with the references, I extracted an area of my
worksheet to a new blank worksheet to experiment. The actual references are
as follows:
Columns A to K contain data irrelevent to this calc. as do rows 1 to 6,
hence day 1 is in the two cells L7:M7 (merged), day 2 N7:O7 and so on.
I have 4 work sheets - 3 months to each, They are all the same except for
days of the month, so I will explain January to March.
Days of the month are in columns L to GW (row 7), each day number is in two
merged cells. This allows two columns below each day number to represent a.m
& p.m. This column range includes sufficient columns to represent a day for
each month, including leap years. For presentation purposes there are also
narrow columns that fill black to show the beginning of the period (for 10
days only), as entered in cell E2, and the end of each the month (and appear
to move for a leap year). So the number of columns does not equal the number
of days.
I need to only count the "W"s between a date entered in E2 and the last day
of the 3 months (31st March) is fixed but the date in E2 is variable.
I cannot make either of your suggested formula variations identify the
column (hence the beginning of the range) along the row that reflects the
date number from cell E2. (The ADDRESS function performs this perfectly in
isolation, but not when combined with COUNTIF !)
This is the first time I have used this Discussion Group, so apologies if
this is long winded, but I have been trying to solve this problem for several
months and your assistance is truely appreciated.
Regards
Ron