Why is the SUM, INDEX, MATCH function not working for one, whilst it works for the other

[ihateexcell]

I am trying to understand why is it if I use the following

=SUM(N3:INDEX(N3:N11,MATCH(TRUE,(M3:M11=""),0)))

, it successfully sums as expected once it hits an empty cell the next row but...

if I change it to

=SUM(N3:INDEX(N3:N11,MATCH(1,(M3:M11),0)))

, it only adds the first value in N column even though M4 and M5 matches the lookup value too
Index is column M while Total is column N

 

[bebo021999]

Please note that the MATCH function returns the first found position and will disregard subsequent values.

 

[arthurbr]

Also note that, with your formula, if there is a value in N6, it will be added in your result even if M6 is empty as INDEX(N3:N11,MATCH(TRUE,(M3:M11=""),0)) returns a reference to N6

 

[Peter_SSs]

it only adds the first value in N column even though M4 and M5 matches the lookup value too

@bebo021999 has explained why your second formula only summed the one value, but to get the sum you seem to be trying for, you could use

=SUMIFS(N3:N11,M3:M11,1)

 

[ihateexcell]

Also note that, with your formula, if there is a value in N6, it will be added in your result even if M6 is empty as INDEX(N3:N11,MATCH(TRUE,(M3:M11=""),0)) returns a reference to N6

Hmm it doesn't though, it'll only be added if M5 has a value, because it's referencing column for an empty cell, once it hits one it stops summing

@bebo021999 has explained why your second formula only summed the one value, but to get the sum you seem to be trying for, you could use

=SUMIFS(N3:N11,M3:M11,1)

Thanks for the alternative, I was figuring out a way to just fill the first cell of Index/M column for each entry e.g. 1 for entry #1, 2 for entry #2 etc but I guess this/the first code will have to do

 

[Peter_SSs]

Hmm it doesn't though

Yes it does ..

23 08 17.xlsm
	L	M	N
1
2
3	1006	1	2
4		1	2
5		1	2
6			1000
7		2	1600
Sample

Cell Formulas
Range	Formula
L3	L3	=SUM(N3:INDEX(N3:N11,MATCH(TRUE,(M3:M11=""),0)))

Thanks for the alternative,

You're welcome.

 

[MARK858]

Yes it does ..

it'll only be added if M5 has a value

sums as expected once it hits an empty cell the next row

I think the OP wants it to sum to the value in column N if column M on the previous row has a value (although not worded well if that is the case in post number 1)

 

[Peter_SSs]

I think the OP wants it to sum to the value in column N if column M on the previous row has a value

Yes, I know what the OP wants. However, @arthurbr pointed out that the very first formula posted (& noted as returning the correct result) would actually fail if there was a value in column N next to the first blank in column M.
I read post #5 as the OP disagreeing with that statement in post #3 so I posted an example to support it.

 

[ihateexcell]

I think the OP wants it to sum to the value in column N if column M on the previous row has a value (although not worded well if that is the case in post number 1)

Actually what I would like to do is to sum N3-N6 without having to fill M4-6 with any values thus, I was trying with INDEX MATCH but wasn't able to get it to work, so in the meantime I'll take @Peter_SSs's SUMIF suggestion as workaround

Yes it does ..

23 08 17.xlsm
	L	M	N
1
2
3	1006	1	2
4		1	2
5		1	2
6			1000
7		2	1600
Sample

Cell Formulas
Range	Formula
L3	L3	=SUM(N3:INDEX(N3:N11,MATCH(TRUE,(M3:M11=""),0)))

My bad there, apparently couldn't count as well; thanks @arthurbr

 

[Peter_SSs]

Actually what I would like to do is to sum N3-N6 without having to fill M4-6 with any values

What about this then?

23 08 18.xlsm
	M	N	O	P	Q
1
2	Index	Total		Index	Sum
3	1	1000		1	1003
4		1		2	1600
5		1
6		1
7	2	1600
8
9
10
11
Sum by Index

Cell Formulas
Range	Formula
Q3:Q4	Q3	=LET(M,M$3:M$11,N,N$3:N$11,fr,MATCH(P3,M,0),lr,IFNA(MATCH(TRUE,INDEX(M,fr+1):M$11<>"",0),ROWS(N)),SUM(INDEX(N,fr):INDEX(N,lr)))