Conditional Formula for excel

Question

I am trying to do addition/subtraction from a total of one column, based on the value of a different column.

Here are the details:

Column H2:H61 has 3 possible values: Complete, Incomplete, Does Not exist.
Column I2:I61 are integers.

What I'm trying to accomplish is, for each Row in column H, evaluate if the value is "Complete". If it is, then, in a running total cell, convert the corresponding Row in I to a negative number and add it to the total. If it isn't, leave the number a positive number and add it to the total.

Example:

H2 = "Complete" I2 = 1.5
h3 = "Incomplete" I3 = 0.5
h4 = "Complete" I4 = 2.0

The total is 3

EDIT

Here is the full scope of it:

Excel Screenshot

So, the total values of I and L is currently 40.

What I'm trying to do is, for example, if H2 = "Complete", then I want to subtract I2 (which is 1.5), which would change the total value to 38.5.

H3 is "Does Not Exist" and != "Complete", so the total would still be 38.5. H4 is "Complete", so the total would be 37.5

so on and so forth. Hope this helps clarify for everyone!

Answer 1

Try following formula in K2 cell then drag and down.

=SUMIF($H$2:$H2,"Complete",$I$2:$I2)-SUMIF($H$2:$H2,"Incomplete",$I$2:$I2)

Answer 2

I guess you want something like:

=40-SUMIF(H:H,"Completed",I:I)

assuming you do not have the 40 total in either Column H or I.

Answer 3

I figured it out. What I did was in a separate cell I made the following formula:

=SUMIF(H2:H61,"Completed",I2:I61)

From there, I used that cell to create the following formula to get the result I wanted:

=P31 - (S30+S31)

The S30 and S31 cells are there because I was counting for each "completed" value in two separate columns.