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 per-row:
=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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
The way I would do this is to add a helper column at the end of your departments that does the individual SUM per-row:
=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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
It worked! Thanks much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!