Formula to add column amounts unless cell is blank then add number in a different column

[utahwilkinsons]

I believe that Excel can do everything but this one has me stumped. I am a self-taught Excel user (so be patient). I've been struggling with this for a whole week.

Here are the 2 things I am trying to accomplish in the attached file
1) Column H Formula: sum the totals in column G unless it is empty. If it is empty then use the amount in column F.
2) Column J Formula: same as the above EXCEPT it should only add the totals if there is an "x" in column I.

Am I asking too much of Excel?

I am also open to other suggestions of collecting this data.

TIA

[AliGW]

Welcome to the forum!

How about this for G?

=SUM(G:G)+SUMIF(G:G,"",F:F)

And for the other:

=SUMIF(I:I,"x",G:G)+SUMIFS(F:F,G:G,"",I:I,"x")

[ben_hensel]

In H4:

Please Login or Register  to view this content.

In J4:

Please Login or Register  to view this content.

These are array formulas, so they must be input with CTRL+SHIFT+ENTER not just ENTER key.

Also, avoid putting merged cells in any sort of formula chain; they should really only be used for table superheadings and stuff.

[utahwilkinsons]

AliGW Thank you so much - your formulas worked perfectly!

ben-hensel Thank you for replying. I saw AliGW's email first so I did not try your formula but I appreciate your tip about merged cells.

I knew that Excel could do this - it seems like it can do anything!!