CODEWITHSUNDEEP
excelsumrowconditional-statements
soft pol
soft pol

Reputation: 1

EXCEL : SUM many rows with condition

I have 3 columns, column A contain value that need to calculate later, and column B contain status ( 0 / score ), column C is going to create SUM function with range, but with condition from first score status to the second one. for example : from this

1| point | status | SUM
2|------------------------
3|   3   | score  | <script here>
4|   2   | 0      | <script here>
5|   5   | 0      | <script here>
6|   9   | 0      | <script here>
7|   1   | score  | <script here>
8|   2   | 0      | <script here>
9|   3   | score  | <script here>

to this : 

1| point | status | SUM
2|------------------------
3|   3   | score  | SUM(A3:A7)
4|   2   | 0      | 0
5|   5   | 0      | 0
6|   9   | 0      | 0
7|   1   | score  | SUM(A7:A9)
8|   2   | 0      | 0
9|   3   | score  | SUM(A9:xx)

for now, this is how i get it the first one, but don't know how to get the second one.

=IF(B3="score";
  SUM(
       ADDRESS(ROW(B3);1;4) :
       <dont know how to detecting rows below that have score value>;"0")

.

=IF(B3="score";SUM(ADDRESS(ROW(B3);1;4):<how>;"0")

Upvotes: 0

Views: 541

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Put this in C2 and copy down (the correct values will not be apparent till the formula is copied all the way down the data.)

=IF(B2=0,0,SUM(A2:$A$1040000)-SUM(C3:$C$1040000))

enter image description here

Upvotes: 1

Related Questions