Using SUMIF for more than one column
I have a time tracker that I am trying to get metrics on for certain categories (Admin, Tickets, Projects). We note what the category is then to the right under departments, we note the amount of time spent per department. I can get my formula to work if I have it look at only one column but not if I give it a series of columns:
=SUMIF(Category3:Category315, "Tickets", ITAdmin4:TC314)
Here are the departments:
So I need for it to look first in cell ITAdmin1 then go through to the end of the sheet to TC314. It's giving me an incorrect argument set. Am I using the wrong formula type?
Best Answer

The way I would do this is to add a helper column at the end of your departments that does the individual SUM perrow:
=SUM(ITAdmin@row:TC@row)
Set this as a column formula so that each row has a total calculation listed.
Then for your SUMIF, you'll use the Helper Column as the source for your SUM, but only if the row says "Tickets":
=SUMIF(Category:Category, "Tickets", [Helper Column]:[Helper Column])
Keep in mind that when you list ranges in a formula they need to be the same size... for example, if you list Category1:Category3 (from row 1 to row 3) then you would need to list the same rows in your other range, like so: [Helper Column]1:[Helper Column]3
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

The way I would do this is to add a helper column at the end of your departments that does the individual SUM perrow:
=SUM(ITAdmin@row:TC@row)
Set this as a column formula so that each row has a total calculation listed.
Then for your SUMIF, you'll use the Helper Column as the source for your SUM, but only if the row says "Tickets":
=SUMIF(Category:Category, "Tickets", [Helper Column]:[Helper Column])
Keep in mind that when you list ranges in a formula they need to be the same size... for example, if you list Category1:Category3 (from row 1 to row 3) then you would need to list the same rows in your other range, like so: [Helper Column]1:[Helper Column]3
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
It worked! Thanks much!
Help Article Resources
Categories
Check out the Formula Handbook template!