SUMIF formula is making me crazy.

EcoPartnering · 07/09/18

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.

p_bureau · 07/10/18

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:

build a 3rd column with the formula '= [Repair Inv. Amount] @row + [Diagnostic Inv. Amount] @row' and then perform the SUMIF on this 3rd column
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.

Paul Newcome · 07/10/18

You would want:

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

EcoPartnering · 07/12/18

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.

SUMIF formula is making me crazy.

Comments

Help Article Resources

Categories