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!

 

[Scott Huish]

See if this works for you:
=COUNTIF(OFFSET(INDEX($A:$IV,,COLUMN()),$L10-1,,$M10-$L10),1)

You can add more columns past IV if you need to.

 

[Aitch]

Thanks for the reply! That doesn't seem to work?

I got the SUBSTITUTE formula from this thread = https://www.mrexcel.com/forum/excel-questions/71978-convert-column-number-letter.html

It works perfectly on its own - but won't work in my formula!

 

[Scott Huish]

How does it not work? It seems to do what you are trying to do. Otherwise please explain what you are trying to accomplish.

Excel 2010
	L	M	N	O	P
1				2	1
2
3
4
5
6
7
8
9
10	10	15		1	4
11				3	4
12				2	4
13				1	1
14				9	7
15				9	7
Sheet1

Cell Formulas
Range	Formula
O1	=COUNTIF(OFFSET(INDEX($A:$IV,,COLUMN()),$L10-1,,$M10-$L10),1)
P1	=COUNTIF(OFFSET(INDEX($A:$IV,,COLUMN()),$L10-1,,$M10-$L10),1)

 

[Aitch]

Sorry! I made a mistake with my original post - L10 should be L10+1

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


I've amended your formula and think it's working perfectly now! I just removed the -1 from $L10-1

Would you mind checking I've done it correctly please?

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


Thank you so much bro!

 

[Scott Huish]

If it works for you, it looks OK to me. Glad I could help.

 

[Aitch]

Hi Scott, could you help me out again please!

I've modified your formula with the SUM function:

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

It calculates perfectly - but for some reason the value it creates is not affected by Conditional Formatting?

I'm using a rule that turns the font red if the value is negative - but the SUM value stays the same colour...

What am I doing wrong?

 

[Scott Huish]

It worked fine for me. What is your Conditional Formatting setting?

 

[Aitch]

Let me look at it again - I'm using a simple rule A1:A less than 0


Also, the formula above for finding the column letter =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

How can I "add" to this so that it increases the column letter?

I mean, when I put the formula in cell A1, it gives value "A" - so in cell B1, how can I calculate A1+3 to give value "D"

 

[Scott Huish]

Why don't you just select the cell range and use a condition of Cell Value is less than 0

It usually isn't necessary to build an address from a text string. What is the purpose of that formula?