Question about Autofill with a formula

[Corben]

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,

 

[Peo Sjoblom]

One way

=COUNTIF(INDEX($B:$IV,1,ROWS($A$1:A1)):INDEX($B:$IV,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

 

[Ken Johnson]

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

 

[Corben]

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

 

[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:$IV,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

 

[Corben]

=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 D50100
etc..

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

 

[Peo Sjoblom]

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]

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]

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

 

[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, 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 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

 

[Ken Johnson]

Hi Corben,

If you typed the formula you must make sure the ROW(A2) parts are not
entered as ROW($A$2) or ROW(A$2). This would stop the formula
incrementing the column number as you fill down and would lead to
identical Counts appearing in column A, they would all be the count of
A1 values in B50:B100.
That's the only thing I can think of that may have caused the formula
to fail at your end.

Ken Johnson

 

[Corben]

Hi Ken, you are right, I did have something wrong in the formula,

I've been testing a few things and found a much easier way after rearraging
the layout of the spreadsheet
I will need to fill in only about 30 rows, but hundreds of columns, which
wont be a problem...

I think I am going to use this formula - (to keep it as simple as i can)
A2 = COUNTIF($A$50:$A$100,A$1)
A3 = COUNTIF($B$50:$B$100,A$1)
A4 = COUNTIF($C$50:$C$100,A$1)

I tried the first three rows like this, and copied to B2, B3, B4 respectively,
and i got the results I wanted. So, all i need to do is fill in the formula
i want for 30 rows, and copy+paste as much as i need to other columns. Only
the criteria will change A$1, next B$1, next C$1...

 

[Ken Johnson]

Hi Corben,

It's entirely academic now since you have changed the sheet structure,
however, just removing the first $ from the last $A$1 to give..

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

is "fillable" in both the vertical and horizontal directions. Vertical
filling changes the column references of the first COUNTIF argument,
while horizontal filling only changes the column reference of the
second COUNTIF argument, which I think is what you were after.

I suppose you will have it all finished well before the dust has
settled

Ken Johnson

 

[Corben]

Hi Ken,
I, of course, did not change my original sheet structure, I had just
started over with a different one.
I was beginning to think it couldn't be done, and I didn't want to take up
too much of anybody's time.
The formula you just posted is exactly what I was looking for.

Thank you for your help.
I'm amazed at how smart and helpful everybody is around here.

 

[Ken Johnson]

Hi Corben,
Thanks for those very kind words.
I'm glad to hear you've had success.
Ken Johnson