SUMIF start/stop
 

Hi,

I need sumif type formula that can find start/stop points.

In col V7:V - category, col AT7:AT - hours (time).

In col AU7:AU I would like to calculate the cumulative time spent on each
category ONLY for categories which are adjacent to each other on the list,
(as in if the categories go 1 1 2 1 et only adds the first two 1's NOT all
three). In the case of where categories are cumulated I need to display the
total in the first cell in the list and the rest empty. If there is only one
category it displays simply that time.

Do you know of which formula I could use to achieve this?
Thanks for your help

Example inputs and outputs-

Col V Col AT Col AU
1 2 6
1 4
2 6 24
2 8
2 10
3 12 12
1 14 14
2 16 16
3 18 138
3 20
3 22
3 24
3 26
3 28

SUMIF start/stop
 

In cell AU7 apply the below formula and copy down as required.

=IF(V7=V6,"",SUM(AT7:INDEX(AT7:$AT$100,MATCH(TRUE, INDEX(V7:$V$100<V7,),)))-INDEX(AT7:$AT$100,MATCH(TRUE,INDEX(V7:$V$100<V7,) ,)))

--
Jacob


"LiAD" wrote:

Hi,

I need sumif type formula that can find start/stop points.

In col V7:V - category, col AT7:AT - hours (time).

In col AU7:AU I would like to calculate the cumulative time spent on each
category ONLY for categories which are adjacent to each other on the list,
(as in if the categories go 1 1 2 1 et only adds the first two 1's NOT all
three). In the case of where categories are cumulated I need to display the
total in the first cell in the list and the rest empty. If there is only one
category it displays simply that time.

Do you know of which formula I could use to achieve this?
Thanks for your help

Example inputs and outputs-

Col V Col AT Col AU
1 2 6
1 4
2 6 24
2 8
2 10
3 12 12
1 14 14
2 16 16
3 18 138
3 20
3 22
3 24
3 26
3 28

SUMIF start/stop
 

Perfect once again

Thanks

"Jacob Skaria" wrote:

In cell AU7 apply the below formula and copy down as required.

=IF(V7=V6,"",SUM(AT7:INDEX(AT7:$AT$100,MATCH(TRUE, INDEX(V7:$V$100<V7,),)))-INDEX(AT7:$AT$100,MATCH(TRUE,INDEX(V7:$V$100<V7,) ,)))

--
Jacob


"LiAD" wrote:

Hi,

I need sumif type formula that can find start/stop points.

In col V7:V - category, col AT7:AT - hours (time).

In col AU7:AU I would like to calculate the cumulative time spent on each
category ONLY for categories which are adjacent to each other on the list,
(as in if the categories go 1 1 2 1 et only adds the first two 1's NOT all
three). In the case of where categories are cumulated I need to display the
total in the first cell in the list and the rest empty. If there is only one
category it displays simply that time.

Do you know of which formula I could use to achieve this?
Thanks for your help

Example inputs and outputs-

Col V Col AT Col AU
1 2 6
1 4
2 6 24
2 8
2 10
3 12 12
1 14 14
2 16 16
3 18 138
3 20
3 22
3 24
3 26
3 28