CODEWITHSUNDEEP
excelrange
Alex_P
Alex_P

Reputation: 2952

Can one count the range between the start of target cell until the end of a target cell?

My data looks as follows. The 1 in column A refers to new application while the 0 are sub-acccesses of the same application. I want to get the range of rows for one application.

     A      B
     IT     Dependency
1    1
2    0      App
3    0
4    0
5    0      File
6    0
7    1      
8    0
9    0
10   1      
11   0
12   0      App

I would like to count a range from A1 to A6, starting afterwards again from A7 to A9, etc..

Initially I thought to use Countif or Lookup but I could not usefully apply it.

Output Expectation

I would like to have the range (i.e. A1:A6) as output or 1 and 6, so I can use it for other formulas (i.e. concatenatemultiple).

Does Excel have a formula counting a varying range?

Upvotes: 1

Views: 88

Answers (2)

EEM
EEM

Reputation: 6659

Need to use a combination of:
MATCH: To calculate the numbers of rows till next Application
INDEX: To define the range for each Application
OFFSET: To define the range for each Application (Volatile)
IFERROR and COUNT: to define the range of the last Application

I seldom use OFFSET because it is volatile nevertheless the formula is there...

Try this formula (note that it uses SUM instead ofCONCATENATEMULTIPLE since it is written in Excel 2013, but should also work in Excel 2016):

Using INDEX: 

 =IF($A2<>1,"",  
 SUM(C2:INDEX($C2:$C$31,  
 IFERROR(MATCH(1,$A3:$A$31,0),  
 COUNT($A2:$A$31)))))

Using OFFSET: 

=IF($A2<>1,"",
SUM(OFFSET($C2,0,0,
IFERROR(MATCH(1,$A3:$A$31,0),
COUNT($A2:$A$31)),1)))

enter image description here

Upvotes: 0

pnuts
pnuts

Reputation: 59475

Maybe in say D2 and copied down to suit:

=IF(A2=0,1+D1,0)

(Assumes labels are in Row1.)

Upvotes: 1

Related Questions