SOLVED

Formulas

Dave_0629
New Contributor

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)

4 Replies

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?

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

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.

Hello,

I want the formula to look down column 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 for House Payments only F2:F7500.

 

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

 

 

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies