Nesting SUMIF formulas; Is it what I need? If so, what did I do wrong?

[jeansb1315]

Hello-
I was using a formula that summed 2 cells on a bank account spreadsheet:
=F214+H213

F is an amount withdrawn/deposited column
H is a running balance of column
So my formula takes the previous balance and sums it with the new withdrawal/deposit being entered, giving the new balance

Now I need to break column F into two columns (F & G)
F is withdrawals
G is deposits
H remains the running balance
On each row either F or G will contain a value and the other will remain blank

My problem is creating a function that will sum F & H, if H is not blank OR sum G & H, if G is not blank

After scouring the internet I have tried many functions unsuccessfully.
I felt like I was getting close when trying to nest SUMIF statements

=SUMIF(F214="","",H215+F214,IF(G214="","",H215+G214))

It gives me the error "You've entered too many arguments for this function"

Can anyone tell me what I'm doing wrong here?
I would appreciate any help on this.
Thank you in advance!

 

[dUBBINS]

You should change it to this:

=IF(F214="",H215+G214,H215+F214)

 

[dUBBINS]

But for education purposes, here is the problem with your formula, you cannot nest SUMIF(). The syntax for SUMIF() is:

=SUMIF(RANGE,CRITERIA,[SUM RANGE])

RANGE = The range that you are looking for the CRITERIA; and if no SUM RANGE is entered, it will serve as both.

Say, you have words like Bird, Bee, and Dog in the column. Then you want to sum every Dog. Then you would need to state which column was the number of Dogs.
Say, you have amounts in the RANGE. Then you want to sum all of the amounts over 100. Then the RANGE would be both the RANGE and SUM RANGE.

CRITERIA = The "trigger" that tells Excel to sum the amount in that row.

Say, you want to sum all of the amounts over 100. The criteria would be ">100".

SUM RANGE = This is the column that will be summed. And again, it is optional, so if it is left out, the RANGE will be the SUM RANGE.



The syntax for IF() functions is:

=IF(TEST,DO IF TRUE, DO IF FALSE)

And you can nest them so if it is FALSE it will do the next IF(), or in other words the next test. It can be very long, but here is a nested IF():

=IF(TEST,DO IF TRUE, =IF(TEST,DO IF TRUE, =IF(TEST,DO IF TRUE, DO IF FALSE)))

 

[Marcílio_Lobão]

jeansb1315, Good evening.

"...I was using a formula that summed 2 cells on a bank account spreadsheet:
=F214+H213..."

As on each row either F or G will contain a value and the other will remain blank,

Try to use:

Before:= H215 + F214

Now...:= SUM(H215, F214, G214)

Is that what you want?
I hope it helps.

 

[jeansb1315]

Thank you!

This returned $0.00 until I tweaked the cells referenced in the calculation to the following:
=IF(F215="",G215+H214,F215+H214)

That was exactly what I needed.
Thank you so very much for the fix!!
My brain was turning to mush.

 

[jeansb1315]

That worked also Marcilio!
I guess there are multiple ways to get this result.
Thank you for the help.

 

[jeansb1315]

Good information. Thank you dUBBINS.

 

[Marcílio_Lobão]

Thanks for the feedback.

Glad to be of help.

Regards from Brazil.

 

[dUBBINS]

Good information. Thank you dUBBINS.

You are quite welcome, J. Happy Friday to you.