# SUMIF formula is making me crazy.

Options
edited 12/09/19

Just when I think I am getting better with formulas I hit a wall with something that seems simple.

I need to add 2 column sums together for the rows that have a RED ball Status.

=SUMIF(Status:Status, "Red", [Repair Inv. Amount]:[Repair Inv. Amount] + [Diagnostic Inv. Amount]:[Diagnostic Inv. Amount])

Then I get an invalid operation message.

Note:  When I shorten the statement to to only include the fist part of the statement it works.  It worked with both column names.

=SUMIF(Status:Status, "Red", [Repair Inv. Amount]:[Repair Inv. Amount])

So then I tried this:

=SUMIF(Status:Status, "Red", [Repair Inv. Amount]:[Repair Inv. Amount], [Diagnostic Inv. Amount]:[Diagnostic Inv. Amount])

Then I get invalid argument set and it blocks another cells...

I am stumped, any suggestions of what I am missing would be greatly appreciated.

Tags:

• Options

Hi,

the SUMIF function only do one thing : sum a column. A single column.

So if you want the results of the sum of 2 columns you have 2 choices:

1. build a 3rd column with the formula '= [Repair Inv. Amount] @row + [Diagnostic Inv. Amount] @row' and then perform the SUMIF on this 3rd column
2. use this formula instead :

=SUMIF(Status:Status, "Red", [Repair Inv. Amount]:[Repair Inv. Amount]) + SUMIF(Status:Status, "Red", [Diagnostic Inv. Amount]:[Diagnostic Inv. Amount]) '

Does this help?

Best Regards,

Paul.

• ✭✭✭✭✭✭
edited 07/10/18
Options

You would want:

=SUMIF(Status:Status, "Red", [Repair Inv. Amount]:[Repair Inv. Amount]) + SUMIF(Status:Status, "Red", [Diagnostic Inv. Amount]:[Diagnostic Inv. Amount])

• Options

Paul, thank you so much.  I have been doing that all wrong.  I did not even think about adding 2 formulas together.   Practice makes perfect.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!