Formulas

[Dave_0629]

Hi I am trying to sort my data in column D (for example House Payment) and either subtract the values in column E or add the values in column F to come up with the total. I am not sure of which function to use and/or how to write the formula. HELP!

This is what I tried: =SUMIFS(D2:D30000,"House Payment",$f$2:$f$30000-$e$2:$e$30000)

[#2]

Hello,

I'm not quite sure what you want to achieve. Sumifs can add all rows where a certain condition is met. For example "Sum all cells in F2:F30000 where D2:D30000 has the text "House Payment" in the same row.

=SUMIFS($f$2:$f$30000,D2:D30000,"House Payment")

I'm not sure what you want to do with the data in column E. Can you maybe post a screenshot and then explain the expected result in simple words?

[#3]

Hello Ingeborg,

So I attached a file for you to look at. So you can now see that in column AC I want the formula to exist. I want the formula to search for only "House Payment" in column D. Subtract all entries for House Payments in column E and Add all entries in column F for a total in column AC.

I hope that I have made myself clear??

Thanks,

Dave

[#4 · Accepted solution]

I assume you want the formula to look for "house payment" in row 2, but copied down it should look for HOA, then Telephone, etc, so you want the formula to look for the text in column J.

You can get all additions like this

=SUMIFS(F:F,D:D,J2)

and you can get all subtractions like this:

=SUMIFS(E:E,D:D,J2)

And if you want to offset them against each other, just subtract one from the other like this:

=SUMIFS(F:F,D:D,J2)-SUMIFS(E:E,D:D,J2)

That will give you the balance for the text in J2. Copy down.

[#5]

Hello,

I want the formula to look down column D for "House Payment" only.

Then in column E total all of the entries for House Payments only E2:E7500- this total will be subtracted from the entries in column F for House Payments only F2:F7500.

This will give me the balance for House Payment (column J) entered into column AC.