How can I count the number of months it takes to reach a total of 100,000.
This would in a row count fashion from left to right.

Eg.
Cells A1:A7 contain sales by month.
Cell A8 has a count of the number of months where the cumulative sum is
equal to or greater than 100,000. So;

10000, 10000, 40000, 35000, 10000, 10000, 10000

The count would be 5

Also if

0, 0, 0, 50000, 50000, 40000,40000, 40000

The count would be 2 as it ignore 0's to the left of the first value.

Bruce

Try the following array formula...

=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,,,SMALL(IF(A1:A7< 0,ROW(A1:A7)-CELL("row
",A1)+1),ROW(INDIRECT("1:"&COUNTIF(A1:A7,"0"))))) )=100000,0)

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"Bruce" wrote:

How can I count the number of months it takes to reach a total of 100,000.
This would in a row count fashion from left to right.

Eg.
Cells A1:A7 contain sales by month.
Cell A8 has a count of the number of months where the cumulative sum is
equal to or greater than 100,000. So;

10000, 10000, 40000, 35000, 10000, 10000, 10000

The count would be 5

Also if

0, 0, 0, 50000, 50000, 40000,40000, 40000

The count would be 2 as it ignore 0's to the left of the first value.

Bruce

I think, confirmed with control+shift+enter...

=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A7)-CELL("Row",A1)+1))=100000,0)

would suffice.

Domenic wrote:
Try the following array formula...

=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,,,SMALL(IF(A1:A7< 0,ROW(A1:A7)-CELL("row
",A1)+1),ROW(INDIRECT("1:"&COUNTIF(A1:A7,"0"))))) )=100000,0)

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"Bruce" wrote:


How can I count the number of months it takes to reach a total of 100,000.
This would in a row count fashion from left to right.

Eg.
Cells A1:A7 contain sales by month.
Cell A8 has a count of the number of months where the cumulative sum is
equal to or greater than 100,000. So;

10000, 10000, 40000, 35000, 10000, 10000, 10000

The count would be 5

Also if

0, 0, 0, 50000, 50000, 40000,40000, 40000

The count would be 2 as it ignore 0's to the left of the first value.

Bruce

Hi Aladin!

Actually, the OP asked to ignore zero values. So I think my formula may
be required.

Although, while the OP did say that zero values prior to the first
non-zero value should be ignored, it didn't say whether subsequent zero
values should be ignored as well.

So maybe my formula may not be appropriate. :)

In article ,
Aladin Akyurek wrote:

I think, confirmed with control+shift+enter...

=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A7)-CELL("Row",A1)+1))=100000,0)

would suffice.

I see I overlooked that...

Still less costly, therefore worth posting:

Not to count empty or zero-valued cells before the first positive value
in an otherwise numeric range:

=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A7)-CELL("Row",A1)+1))=100000,0)-MATCH(TRUE,A1:A70,0)+1

or, if needed, to exlcude text values:

=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A7)-CELL("Row",A1)+1))=100000,0)-MATCH(1,ISNUMBER(A1:A7)*(A1:A70),0)+1

The formulas still need to be confirmed with control+shift+enter.
Domenic wrote:
Hi Aladin!

Actually, the OP asked to ignore zero values. So I think my formula may
be required.

Although, while the OP did say that zero values prior to the first
non-zero value should be ignored, it didn't say whether subsequent zero
values should be ignored as well.

So maybe my formula may not be appropriate. :)

In article ,
Aladin Akyurek wrote:


I think, confirmed with control+shift+enter...

=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A7 )-CELL("Row",A1)+1))=100000,0)

would suffice.

Nice! :)

In article ,
Aladin Akyurek wrote:

I see I overlooked that...

Still less costly, therefore worth posting:

Not to count empty or zero-valued cells before the first positive value
in an otherwise numeric range:

=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A7)-CELL("Row",A1)+1))=100000,0)-
MATCH(TRUE,A1:A70,0)+1

or, if needed, to exlcude text values:

=MATCH(TRUE,SUBTOTAL(9,OFFSET(A1,0,0,ROW(A1:A7)-CELL("Row",A1)+1))=100000,0)-
MATCH(1,ISNUMBER(A1:A7)*(A1:A70),0)+1

The formulas still need to be confirmed with control+shift+enter.