Why this INDEX/MATCH function does not work?

[excelos]

Hello!

I have this function:

=SUM(INDEX($C$212:$AM$212,1,MATCH(TRUE,$B$263<C3:AM3,0)):$AM$212)

It should calculate the sum of a range of cells.

That range is defined by the INDEX/MATCH function.
The end of the range is easy, I want it to be $AM$212.
The start of the range is tricky. I want it to be the cell of the range $C$212:$AM$212 where the first cell of the range C3:AM3 is bigger than $B$263.


However, it does not work. It returns N/A. Any idea?

Thanks!

 

[joeu2004]

Unfortunately, I think your formula got mangled by this forum's defects (poor design). Put spaces around comparison operators like " > ", " < ", etc. Actually, I think it is only one of those that causes problems. I never remember which one. And why take chances?

I might write the following formula, which must be array-entered (press ctrl+shift+Enter instead of just Enter):

=IFERROR(SUM(INDEX(C212:AM212, 1, MATCH(TRUE, C3:AM3 > B263, 0)):AM212), 0)

It is unclear if and where you need to use absolute references (with "$"). Only if you intend to copy the formula.

IFERROR covers the case when no match occurs. If you know the match always works, IFERROR(...,0) is unnecessary.

PS.... And you are very wise to use the form INDEX(...):AM212 instead of OFFSET(). The latter is a volatile function, so the SUM formula and any dependent cells will be recalculated whenever the worksheet is recalculated; for example, whenever any cell in the workbook is edited.

 

[AhoyNC]

Try:

Excel Workbook
	C	D	E	F	G	H	AL	AM
212	0		0	6	7	0	XXX	6
213		XXX					XXX
214	19
Sheet

 

[Eric W]

This question appears to be a restatement of the same question from these 2 threads:

https://www.mrexcel.com/forum/excel...sible-identify-cell-use-its-co-ordinates.html
https://www.mrexcel.com/forum/excel...-right-starting-point-sum-based-criteria.html

Did not my response yesterday work for you? Or do you just desire a formula using INDEX in some way? I hope you find something that works for you, but try not to be hung up on the specific method. Sometimes there is a non-intuitive way that works better.

 

[excelos]

Try:

Excel Workbook
	C	D	E	F	G	H	AL	AM
212	0		0	6	7	0	XXX	6
213		XXX					XXX
214	19
Sheet

Thanks, that's very close to what I want.

However, I want it to do a check in a different row and calculate the sum in a different row.
It's confusing where to specify each row.

Can you make it calculate the sum in the row 213, based on the range specified in row 212?

Thanks!

 

[excelos]

This question appears to be a restatement of the same question from these 2 threads:

https://www.mrexcel.com/forum/excel...sible-identify-cell-use-its-co-ordinates.html
https://www.mrexcel.com/forum/excel...-right-starting-point-sum-based-criteria.html

Did not my response yesterday work for you? Or do you just desire a formula using INDEX in some way? I hope you find something that works for you, but try not to be hung up on the specific method. Sometimes there is a non-intuitive way that works better.

Unfortunately it was not what I was after, maybe I didn't word it properly.