software

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
quot;It is a good thing to follow the first law of holes;
if you are in one stop digging.quot; Lord Healeyquot;Corbenquot; gt; wrote in message
...
gt;I want to look at a range of cells, but have the column range change, while
gt; the row range stays the same.
gt;
gt; ex:
gt; in A2= COUNTIF(B50:B100,A1)
gt; in A3 I would like =COUNTIF(C50:C100,A1)
gt;
gt; If i copy A2 to A3 though, it changes the formula to =COUNTIF
gt; (C51:C101,A1)
gt;
gt; Thanks,
Another way

=COUNTIF(INDIRECT(ADDRESS(50,ROW(A2))amp;quot;:quot;amp;ADDRESS( 100,ROW(A2))),$A$1)

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

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

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

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

quot;Peo Sjoblomquot; wrote:

gt; One way
gt;
gt; =COUNTIF(INDEX($B:$IV,1,ROWS($A$1:A1)):INDEX($B:$I V,50,ROWS($A$1:A1)),$A$1)
gt;
gt; if could be done a little bit shorter using OFFSET but that formula would be
gt; volatile
gt;
gt; =COUNTIF(OFFSET($B$1,0,ROWS($A$1:A1)-1,50,),$A$1)
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Excel 95 - Excel 2007
gt; Nothwest Excel Solutions
gt; www.nwexcelsolutions.com
gt; quot;It is a good thing to follow the first law of holes;
gt; if you are in one stop digging.quot; Lord Healey
gt;
gt;
gt; quot;Corbenquot; gt; wrote in message
gt; ...
gt; gt;I want to look at a range of cells, but have the column range change, while
gt; gt; the row range stays the same.
gt; gt;
gt; gt; ex:
gt; gt; in A2= COUNTIF(B50:B100,A1)
gt; gt; in A3 I would like =COUNTIF(C50:C100,A1)
gt; gt;
gt; gt; If i copy A2 to A3 though, it changes the formula to =COUNTIF
gt; gt; (C51:C101,A1)
gt; gt;
gt; gt; Thanks,
gt;
gt;
gt;

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))amp;quot;:quot;amp;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))amp;quot;:quot;amp;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 D50100
etc..

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

quot;Ken Johnsonquot; wrote:

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

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
quot;It is a good thing to follow the first law of holes;
if you are in one stop digging.quot; Lord Healeyquot;Corbenquot; gt; wrote in message
...
gt; Thanks,
gt; i will give this a try.
gt; I'm sorry, I was incorrect with my example in the first message.
gt;
gt; When I stated
gt; quot;If i copy A2 to A3 though, it changes the formula to =COUNTIF
gt; (C51:C101,A1)quot;
gt;
gt; - that should have read..
gt; If i copy A2 to A3 though, it changes the formula to =COUNTIF
gt;gt; gt; (B51:B101,A1)
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; One way
gt;gt;
gt;gt; =COUNTIF(INDEX($B:$IV,1,ROWS($A$1:A1)):INDEX($B:$I V,50,ROWS($A$1:A1)),$A$1)
gt;gt;
gt;gt; if could be done a little bit shorter using OFFSET but that formula would
gt;gt; be
gt;gt; volatile
gt;gt;
gt;gt; =COUNTIF(OFFSET($B$1,0,ROWS($A$1:A1)-1,50,),$A$1)
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; Excel 95 - Excel 2007
gt;gt; Nothwest Excel Solutions
gt;gt; www.nwexcelsolutions.com
gt;gt; quot;It is a good thing to follow the first law of holes;
gt;gt; if you are in one stop digging.quot; Lord Healey
gt;gt;
gt;gt;
gt;gt; quot;Corbenquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I want to look at a range of cells, but have the column range change,
gt;gt; gt;while
gt;gt; gt; the row range stays the same.
gt;gt; gt;
gt;gt; gt; ex:
gt;gt; gt; in A2= COUNTIF(B50:B100,A1)
gt;gt; gt; in A3 I would like =COUNTIF(C50:C100,A1)
gt;gt; gt;
gt;gt; gt; If i copy A2 to A3 though, it changes the formula to =COUNTIF
gt;gt; gt; (C51:C101,A1)
gt;gt; gt;
gt;gt; gt; Thanks,
gt;gt;
gt;gt;
gt;gt;
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.

quot;Peo Sjoblomquot; wrote:

gt; Sorry, I misuderstood, I thought you wanted B1:B50, next C1:C50 but you want
gt; B2:B50, B51:B100 and so on
gt;
gt; =COUNTIF(OFFSET($B$1,ROWS($A$1:A1)*50-50,,50,),$A$1)
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Excel 95 - Excel 2007
gt; Nothwest Excel Solutions
gt; www.nwexcelsolutions.com
gt; quot;It is a good thing to follow the first law of holes;
gt; if you are in one stop digging.quot; Lord Healey
gt;
gt;
gt; quot;Corbenquot; gt; wrote in message
gt; ...
gt; gt; Thanks,
gt; gt; i will give this a try.
gt; gt; I'm sorry, I was incorrect with my example in the first message.
gt; gt;
gt; gt; When I stated
gt; gt; quot;If i copy A2 to A3 though, it changes the formula to =COUNTIF
gt; gt; (C51:C101,A1)quot;
gt; gt;
gt; gt; - that should have read..
gt; gt; If i copy A2 to A3 though, it changes the formula to =COUNTIF
gt; gt;gt; gt; (B51:B101,A1)
gt; gt;
gt; gt; quot;Peo Sjoblomquot; wrote:
gt; gt;
gt; gt;gt; One way
gt; gt;gt;
gt; gt;gt; =COUNTIF(INDEX($B:$IV,1,ROWS($A$1:A1)):INDEX($B:$I V,50,ROWS($A$1:A1)),$A$1)
gt; gt;gt;
gt; gt;gt; if could be done a little bit shorter using OFFSET but that formula would
gt; gt;gt; be
gt; gt;gt; volatile
gt; gt;gt;
gt; gt;gt; =COUNTIF(OFFSET($B$1,0,ROWS($A$1:A1)-1,50,),$A$1)
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt;
gt; gt;gt; Regards,
gt; gt;gt;
gt; gt;gt; Peo Sjoblom
gt; gt;gt;
gt; gt;gt; Excel 95 - Excel 2007
gt; gt;gt; Nothwest Excel Solutions
gt; gt;gt; www.nwexcelsolutions.com
gt; gt;gt; quot;It is a good thing to follow the first law of holes;
gt; gt;gt; if you are in one stop digging.quot; Lord Healey
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Corbenquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;I want to look at a range of cells, but have the column range change,
gt; gt;gt; gt;while
gt; gt;gt; gt; the row range stays the same.
gt; gt;gt; gt;
gt; gt;gt; gt; ex:
gt; gt;gt; gt; in A2= COUNTIF(B50:B100,A1)
gt; gt;gt; gt; in A3 I would like =COUNTIF(C50:C100,A1)
gt; gt;gt; gt;
gt; gt;gt; gt; If i copy A2 to A3 though, it changes the formula to =COUNTIF
gt; gt;gt; gt; (C51:C101,A1)
gt; gt;gt; gt;
gt; gt;gt; gt; Thanks,
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

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
quot;It is a good thing to follow the first law of holes;
if you are in one stop digging.quot; Lord Healeyquot;Peo Sjoblomquot; gt; wrote in message
...
gt; Sorry, I misuderstood, I thought you wanted B1:B50, next C1:C50 but you
gt; want B2:B50, B51:B100 and so on
gt;
gt; =COUNTIF(OFFSET($B$1,ROWS($A$1:A1)*50-50,,50,),$A$1)
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Excel 95 - Excel 2007
gt; Nothwest Excel Solutions
gt; www.nwexcelsolutions.com
gt; quot;It is a good thing to follow the first law of holes;
gt; if you are in one stop digging.quot; Lord Healey
gt;
gt;
gt; quot;Corbenquot; gt; wrote in message
gt; ...
gt;gt; Thanks,
gt;gt; i will give this a try.
gt;gt; I'm sorry, I was incorrect with my example in the first message.
gt;gt;
gt;gt; When I stated
gt;gt; quot;If i copy A2 to A3 though, it changes the formula to =COUNTIF
gt;gt; (C51:C101,A1)quot;
gt;gt;
gt;gt; - that should have read..
gt;gt; If i copy A2 to A3 though, it changes the formula to =COUNTIF
gt;gt;gt; gt; (B51:B101,A1)
gt;gt;
gt;gt; quot;Peo Sjoblomquot; wrote:
gt;gt;
gt;gt;gt; One way
gt;gt;gt;
gt;gt;gt; =COUNTIF(INDEX($B:$IV,1,ROWS($A$1:A1)):INDEX($B:$I V,50,ROWS($A$1:A1)),$A$1)
gt;gt;gt;
gt;gt;gt; if could be done a little bit shorter using OFFSET but that formula
gt;gt;gt; would be
gt;gt;gt; volatile
gt;gt;gt;
gt;gt;gt; =COUNTIF(OFFSET($B$1,0,ROWS($A$1:A1)-1,50,),$A$1)
gt;gt;gt;
gt;gt;gt; --
gt;gt;gt;
gt;gt;gt; Regards,
gt;gt;gt;
gt;gt;gt; Peo Sjoblom
gt;gt;gt;
gt;gt;gt; Excel 95 - Excel 2007
gt;gt;gt; Nothwest Excel Solutions
gt;gt;gt; www.nwexcelsolutions.com
gt;gt;gt; quot;It is a good thing to follow the first law of holes;
gt;gt;gt; if you are in one stop digging.quot; Lord Healey
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; quot;Corbenquot; gt; wrote in message
gt;gt;gt; ...
gt;gt;gt; gt;I want to look at a range of cells, but have the column range change,
gt;gt;gt; gt;while
gt;gt;gt; gt; the row range stays the same.
gt;gt;gt; gt;
gt;gt;gt; gt; ex:
gt;gt;gt; gt; in A2= COUNTIF(B50:B100,A1)
gt;gt;gt; gt; in A3 I would like =COUNTIF(C50:C100,A1)
gt;gt;gt; gt;
gt;gt;gt; gt; If i copy A2 to A3 though, it changes the formula to =COUNTIF
gt;gt;gt; gt; (C51:C101,A1)
gt;gt;gt; gt;
gt;gt;gt; gt; Thanks,
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;
gt;
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, D50100 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 Specialgt; 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