SUMIF formula is making me crazy.
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.
Comments
-
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.
-
You would want:
=SUMIF(Status:Status, "Red", [Repair Inv. Amount]:[Repair Inv. Amount]) + SUMIF(Status:Status, "Red", [Diagnostic Inv. Amount]:[Diagnostic Inv. Amount])
-
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
Categories
Check out the Formula Handbook template!