I want to look at a range of cells, but have the column range change, while
the row range stays the same.

ex:
in A2= COUNTIF(B50:B100,A1)
in A3 I would like =COUNTIF(C50:C100,A1)

If i copy A2 to A3 though, it changes the formula to =COUNTIF (C51:C101,A1)

Thanks,

One way

=COUNTIF(INDEX($B:$IV,1,ROWS($A$1:A1)):INDEX($B:$I V,50,ROWS($A$1:A1)),$A$1)

if could be done a little bit shorter using OFFSET but that formula would be
volatile

=COUNTIF(OFFSET($B$1,0,ROWS($A$1:A1)-1,50,),$A$1)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Nothwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Corben" wrote in message
...
I want to look at a range of cells, but have the column range change, while
the row range stays the same.

ex:
in A2= COUNTIF(B50:B100,A1)
in A3 I would like =COUNTIF(C50:C100,A1)

If i copy A2 to A3 though, it changes the formula to =COUNTIF
(C51:C101,A1)

Thanks,

Another way

=COUNTIF(INDIRECT(ADDRESS(50,ROW(A2))&":"&ADDRESS( 100,ROW(A2))),$A$1)

One more function call than Peo's, so supplied only for your
amusement:-)

Ken Johnson

Actually, I tried out Peo's and I get a different result. I filled
C50:C100 with the same value as in A1 and...
=COUNTIF(INDIRECT(ADDRESS(50,ROW(A2))&":"&ADDRESS( 100,ROW(A2))),$A$1)
returned 51, while...
=COUNTIF(INDEX($B:$IV,1,ROWS($A$1:A2)):INDEX($B:$I V,50,ROWS($A$1:A2)),$A$1)
and =COUNTIF(OFFSET($B$1,0,ROWS($A$1:A1)-1,50,),$A$1)
both returned 1.

Ken Johnson

=COUNTIF(INDIRECT(ADDRESS(50,ROW(A2))&":"&ADDRESS( 100,ROW(A2))),$A$1)

This one works well for the first cell A2 looks in range B50:B100
Can you autofill other rows with the same formula?

A3 = looks in C50:C100
A4 = looks in D50:D100
etc..

I tired it filling B50:B100 all with A1, and then filled C50:C100 with only
40
they both returned 51.

"Ken Johnson" wrote:

Actually, I tried out Peo's and I get a different result. I filled
C50:C100 with the same value as in A1 and...
=COUNTIF(INDIRECT(ADDRESS(50,ROW(A2))&":"&ADDRESS( 100,ROW(A2))),$A$1)
returned 51, while...
=COUNTIF(INDEX($B:$IV,1,ROWS($A$1:A2)):INDEX($B:$I V,50,ROWS($A$1:A2)),$A$1)
and =COUNTIF(OFFSET($B$1,0,ROWS($A$1:A1)-1,50,),$A$1)
both returned 1.

Ken Johnson

Hi Corben,
Sorry, I've been out eating Easter eggs!
I just pasted the formula into a sheet with different numbers of the A1
value in B50:B100,
C50:C100, D50:D100 etc up to column L. After filling the formula down
to A12, A2 showed the correct count for B50:B100, A3 showed the correct
count for C50:C100, etc... up to A12 showing the correct count for
L50:L100.
You must be doing something wrong.
Did you type in the formula or paste it in (Paste Special Text)
(sometimes there's a space before the = that needs to be removed)?
If you typed it in try again, this time copy and paste. I know for sure
it works.

Ken Johnson

Thanks,
i will give this a try.
I'm sorry, I was incorrect with my example in the first message.

When I stated
"If i copy A2 to A3 though, it changes the formula to =COUNTIF
(C51:C101,A1)"

- that should have read..
If i copy A2 to A3 though, it changes the formula to =COUNTIF
(B51:B101,A1)

"Peo Sjoblom" wrote:

One way

=COUNTIF(INDEX($B:$IV,1,ROWS($A$1:A1)):INDEX($B:$I V,50,ROWS($A$1:A1)),$A$1)

if could be done a little bit shorter using OFFSET but that formula would be
volatile

=COUNTIF(OFFSET($B$1,0,ROWS($A$1:A1)-1,50,),$A$1)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Nothwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Corben" wrote in message
...
I want to look at a range of cells, but have the column range change, while
the row range stays the same.

ex:
in A2= COUNTIF(B50:B100,A1)
in A3 I would like =COUNTIF(C50:C100,A1)

If i copy A2 to A3 though, it changes the formula to =COUNTIF
(C51:C101,A1)

Thanks,

Sorry, I misuderstood, I thought you wanted B1:B50, next C1:C50 but you want
B2:B50, B51:B100 and so on

=COUNTIF(OFFSET($B$1,ROWS($A$1:A1)*50-50,,50,),$A$1)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Nothwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Corben" wrote in message
...
Thanks,
i will give this a try.
I'm sorry, I was incorrect with my example in the first message.

When I stated
"If i copy A2 to A3 though, it changes the formula to =COUNTIF
(C51:C101,A1)"

- that should have read..
If i copy A2 to A3 though, it changes the formula to =COUNTIF
(B51:B101,A1)

"Peo Sjoblom" wrote:

One way

=COUNTIF(INDEX($B:$IV,1,ROWS($A$1:A1)):INDEX($B:$I V,50,ROWS($A$1:A1)),$A$1)

if could be done a little bit shorter using OFFSET but that formula would
be
volatile

=COUNTIF(OFFSET($B$1,0,ROWS($A$1:A1)-1,50,),$A$1)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Nothwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Corben" wrote in message
...
I want to look at a range of cells, but have the column range change,
while
the row range stays the same.

ex:
in A2= COUNTIF(B50:B100,A1)
in A3 I would like =COUNTIF(C50:C100,A1)

If i copy A2 to A3 though, it changes the formula to =COUNTIF
(C51:C101,A1)

Thanks,

actually you were closer the first time.
The range I want is B50:B100, next C50:C100 and so on...
I should have explained in more detail or provided a sample worksheet.

I think I have found a more simple solution, especially since I will need to
show some other people how it works in case it needs to be modified.

Thanks for your time.

"Peo Sjoblom" wrote:

Sorry, I misuderstood, I thought you wanted B1:B50, next C1:C50 but you want
B2:B50, B51:B100 and so on

=COUNTIF(OFFSET($B$1,ROWS($A$1:A1)*50-50,,50,),$A$1)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Nothwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Corben" wrote in message
...
Thanks,
i will give this a try.
I'm sorry, I was incorrect with my example in the first message.

When I stated
"If i copy A2 to A3 though, it changes the formula to =COUNTIF
(C51:C101,A1)"

- that should have read..
If i copy A2 to A3 though, it changes the formula to =COUNTIF
(B51:B101,A1)

"Peo Sjoblom" wrote:

One way

=COUNTIF(INDEX($B:$IV,1,ROWS($A$1:A1)):INDEX($B:$I V,50,ROWS($A$1:A1)),$A$1)

if could be done a little bit shorter using OFFSET but that formula would
be
volatile

=COUNTIF(OFFSET($B$1,0,ROWS($A$1:A1)-1,50,),$A$1)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Nothwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Corben" wrote in message
...
I want to look at a range of cells, but have the column range change,
while
the row range stays the same.

ex:
in A2= COUNTIF(B50:B100,A1)
in A3 I would like =COUNTIF(C50:C100,A1)

If i copy A2 to A3 though, it changes the formula to =COUNTIF
(C51:C101,A1)

Thanks,

Doh! B1:B50, B51:B100, B101:B150 that's what the latest offset formula will
do, a non volatile version

=COUNTIF(INDEX($B:$B,ROWS($A$1:A1)*50-49):INDEX($B:$B,ROWS($A$1:A1)*50),$A$1)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Peo Sjoblom" wrote in message
...
Sorry, I misuderstood, I thought you wanted B1:B50, next C1:C50 but you
want B2:B50, B51:B100 and so on

=COUNTIF(OFFSET($B$1,ROWS($A$1:A1)*50-50,,50,),$A$1)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Nothwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Corben" wrote in message
...
Thanks,
i will give this a try.
I'm sorry, I was incorrect with my example in the first message.

When I stated
"If i copy A2 to A3 though, it changes the formula to =COUNTIF
(C51:C101,A1)"

- that should have read..
If i copy A2 to A3 though, it changes the formula to =COUNTIF
(B51:B101,A1)

"Peo Sjoblom" wrote:

One way

=COUNTIF(INDEX($B:$IV,1,ROWS($A$1:A1)):INDEX($B:$I V,50,ROWS($A$1:A1)),$A$1)

if could be done a little bit shorter using OFFSET but that formula
would be
volatile

=COUNTIF(OFFSET($B$1,0,ROWS($A$1:A1)-1,50,),$A$1)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Nothwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Corben" wrote in message
...
I want to look at a range of cells, but have the column range change,
while
the row range stays the same.

ex:
in A2= COUNTIF(B50:B100,A1)
in A3 I would like =COUNTIF(C50:C100,A1)

If i copy A2 to A3 though, it changes the formula to =COUNTIF
(C51:C101,A1)

Thanks,