count the # of entries when sum matches the target
 

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!

count the # of entries when sum matches the target
 

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!

count the # of entries when sum matches the target
 

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)

count the # of entries when sum matches the target
 

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?

count the # of entries when sum matches the target
 

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)