Reference for a range

  • Thread starter Thread starter Gary Thomson
  • Start date Start date
G

Gary Thomson

I have the days of the month in row 2, starting in column
A, i.e. March-1, March-2, March-3, etc. These will change
according to which month it is, and so the last day of the
month will fall in a different column dependant on the
month it is (i.e. AE for March, AD for April, etc).

In rows 3 through 500, I have data which relates to each
of these days, and I want to count the number of times
that, say, "x" appears ("x" can be part of a text string,
so I am using the SUMPRODUCT and FIND functions).

At the moment, I have the following formula:

=SUMPRODUCT((ISERROR(FIND(A505,$AE$3:$AE$500))=FALSE)*1),

entered as an array formula (where I have an "x" in cell
A505).

This works fine, but when I change the month to April, it
is picking up the data from the wrong cells.

How can I get this to work automatically?
Do I have to name the range first or something?
 
Gary Thomson said:
I have the days of the month in row 2, starting in column
A, i.e. March-1, March-2, March-3, etc. These will change
according to which month it is, and so the last day of the
month will fall in a different column dependant on the
month it is (i.e. AE for March, AD for April, etc).

In rows 3 through 500, I have data which relates to each
of these days, and I want to count the number of times
that, say, "x" appears ("x" can be part of a text string,
so I am using the SUMPRODUCT and FIND functions).

At the moment, I have the following formula:

=SUMPRODUCT((ISERROR(FIND(A505,$AE$3:$AE$500))=FALSE)*1),

entered as an array formula (where I have an "x" in cell
A505).

This works fine, but when I change the month to April, it
is picking up the data from the wrong cells.

How can I get this to work automatically?
Do I have to name the range first or something?
Click to expand...

Do the "x"s only appear in the column for the last day of the month? How do
they get there?
When you "change the month", what do you do? Do you re-enter dates in row 2,
or are these derived from formulas?
 
Gary,

Why doesn't it work? I would have thought that in April, column AE will be
blank, so it will just count zero.

The formula can be simplified as

=SUMPRODUCT(--($AE$3:$AE$500=A505))

and it does not need to be an array formula as SUMPRODUCT works on arrays.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
The days of the month are generated by entering the start
of the month (e.g. 1-november-2003) in a cell on another
sheet.

The first date of the month is then transferred from this
cell to cell A2 on the sheet I am using. Cell B2 is
=A2+1; similarly, cell C2 is B2+1; this is copied across
to give all the days of the month.

If the month only has 30 days, the 31st column will show
the first day of the next month, but the user will know
not to enter data in this column. However, the formula
will still pick up the data from the 31st column.

If the month only has 30 days, I want the FIND function to
count the entries in the 30th column, NOT the 31st.

The "x"'s appear in all columns (i.e. they can appear in
each day of the month), but there can also be other
letters in there as well (i.e. "y"'s and "z"'s etc)

Any ideas??
 
Try this:
=SUMPRODUCT(--(OFFSET($A$3,0,DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-1,497,1)=A505
))
This should also work for February, which will have 28 or 29 days.
 
Here's an example of how to get the right number of columns,

=SUMPRODUCT(--(OFFSET($A$2,,,,DAY(DATE(YEAR(Sheet2!$A$1),MONTH(Sheet2!&A$1)+
1,0)))>5))

this part returns the number of days (which I assume are the number of
columns)

DAY(DATE(YEAR(Sheet2!$A$1),MONTH(Sheet2!&A$1)+1,0)

and if you put 04/01/03 in A1 sheet2 the above will return 30

so the first formula would count how many values in A2:AD2 are greater than
5 (just an example since I am not sure what you are doing) Note also that
neither formula has to be array entered, that includes your sumproduct
formula as well
You can rewrite your formula a bit

=SUMPRODUCT(--(ISNUMBER(FIND(A505,$AE$3:$AE$500))))

should return the same a bit more efficient
 
Hi Peo,

Thanx for the formulas!!

The final formula (=SUMPRODUCT(--(ISNUMBER(FIND
(A505,$AE$3:$AE$500))))) works, but the $AE$3:$AE$500 will
calculate correctly when the month has 31 days (i.e.
March), but will not work if the month has 30 days (i.e.
if the month in question is April, the formula will count
the number of times "x" appears on the 1st May, instead of
on the 30th April).

The dates change automatically, but the "x"'s and other
letters in the range $A$3:$AE$500 are input manually.

Any Ideas?
 
Did you try my suggested formula?
=SUMPRODUCT(--(OFFSET($A$3,0,DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-1,497,1)=A505
))
If so, how did you get on with it?
 
Yeah, I tried it but it isn't working...

I am not really sure what I ma doing wrong herre
guys...any help would be greatly appreciated..
 
If you select a part of a formula in the formula bar and press F9, you will
see the result of that part of the formula. (Press Esc to get out of this
mode without altering anything.) This is useful for fault-finding.

In this way, if you select
DAY(DATE(YEAR(A2),MONTH(A2)+1,0))
in my formula and press F9, you should get the number of days in the month
you are working with. Is this so?

When you have got that far, temporarily change the 497 to something more
manageable, such as 10. Then select
OFFSET($A$3,0,DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-1,497,1)
and press F9 and you should see a list of the contents of the first 10 rows
in the last day of the month. Is this so?

Then select
OFFSET($A$3,0,DAY(DATE(YEAR(A2),MONTH(A2)+1,0))-1,497,1)=A505
and press F9 and you should see 10 TRUE/FALSE values....

And so on. This is the way to troubleshoot formulas.
 
Back
Top