count the # of entries when sum matches the target

[maxim.morozov]

Dear all,

How to count the number of integers when sum of those, calculated from
left to right, meets a predefined target

E.g:
Range: 50, 100, 200, 100, 150, 300
Target: 450
Function should return 4

Thanks!

 

[Biff]

Hi!

Use a helper row:

Assume your values are in the range A1:F1

In A2 enter this formula:

=SUM($A1:A1)

Copy across to F2

A5 = target value = 450

=INDEX(COLUMN(A2:F2),MATCH(A5,A2:F2,0))

Biff

 

[Harlan Grove]

Biff wrote...

Assume your values are in the range A1:F1

In A2 enter this formula:

=SUM($A1:A1)

Copy across to F2

A5 = target value = 450

=INDEX(COLUMN(A2:F2),MATCH(A5,A2:F2,0))

....

No ancillary cells needed. Also, why the INDEX call? Would the MATCH
call return 4? Indeed, move (*cut* & paste) A1:F1 into AA1:AF1. Then
what does your formula return?

Anyway, one single cell alternative would be the array formula

=MATCH(A5,MMULT(A1:F1,--(COLUMN(A1:F1)>=TRANSPOSE(COLUMN(A1:F1)))),0)

 

[MaximM]

Dear Harlan, thanks for really elegant solution. Please let me ask one
and maybe silly question: what "--" means in this part of function
--(COLUMN(A1:F1)... ? Why odd numebr of "-" returnes 6 and even return
4, as far as studued example in concerned?

 

[Biff]

why the INDEX call?

That is kind of superfluous, isn't it?

Biff