SUMIF formula is making me crazy.

EcoPartnering

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

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.

 

 

Paul Newcome

You would want:

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

EcoPartnering

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.