Why is this not working?

[Aitch]

Hi! I just can't figure this one out so hope someone here can help!


This straightforward formula works fine:

=COUNTIF( INDIRECT( "Z" & L10 & ":Z" &M10),"=1")


I want to replace the "Z" part with this formula that automatically finds the column - SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")


So shouldn't it look like this?

=COUNTIF( INDIRECT( SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","") & L10 & :SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","") &M10),"=1")


But I keep getting errors - am I missing a "" or ) somewhere?


Thanks!

 

[Aitch]

I'm looking to use Column letters with the INDIRECT function like this:

=INDIRECT("C"&($A1),"")

But want to replace the "C" with a formula that looks at the column it is in and moves across 3 columns to F etc

So I can then copy/paste this across a number of columns without having to manually input the new "C" each time

 

[Scott Huish]

Don't use INDIRECT.

=INDEX($A:$IV,$A$1,3*(COLUMNS($A$1:B1)-COLUMN($A$1)+1))

Copy across.

Excel 2010
	A	B	C	D	E	F	G	H	I
1	7		15	19	22
2
3
4
5
6
7			15	16	18	19	20	21	22
Sheet1

Cell Formulas
Range	Formula
C1	=INDEX($A:$IV,$A$1,3*(COLUMNS($A$1:A1)-COLUMN($A$1)+1))
D1	=INDEX($A:$IV,$A$1,3*(COLUMNS($A$1:B1)-COLUMN($A$1)+1))
E1	=INDEX($A:$IV,$A$1,3*(COLUMNS($A$1:C1)-COLUMN($A$1)+1))

 

[Aitch]

You are a genius dude!

Does this work only relative to the A column? Can I force it to start from a different column?


Also, your previous formula =COUNTIF(OFFSET(INDEX($A:$IV,,COLUMN()),$L10,,$M10-$L10),1)
How can I modify this to count the number of 2 values instead of 1?
And how do I change it to count one column to the left?


PS: Why do my posts keep having different fonts lol?

 

[Scott Huish]

You had said Column C, that's what I did. However just change the first reference in the formula.
To work in relation to Column C for example:
=INDEX($C:$IV,$A$1,3*(COLUMNS($A$1:A1)-COLUMN($A$1)+1))
Copy across.

The last value in a COUNTIF formula is what you are looking for, so change it to 2.
=COUNTIF(OFFSET(INDEX($A:$IV,,COLUMN()),$L10,,$M10-$L10),2)

=COUNTIF(OFFSET(INDEX($A:$IV,,COLUMN()-1),$L10-1,,$M10-$L10),1)

 

[Aitch]

Works perfectly, thanks man!