Feb 03 2019 09:34 AM - edited Feb 03 2019 09:46 AM
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)
Feb 03 2019 11:41 AM - edited Feb 03 2019 02:41 PM
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?
Feb 03 2019 02:22 PM
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
Feb 03 2019 02:44 PM
SolutionI 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.
Feb 03 2019 02:58 PM
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.
Feb 03 2019 02:44 PM
SolutionI 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.