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!

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

wrote in message
oups.com...
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 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)

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?

why the INDEX call?

That is kind of superfluous, isn't it?

Biff

"Harlan Grove" wrote in message
oups.com...
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)