Need help with a sumifs formula
I need to figure out why my "sumifs" formula is not working. I want to total a duration column if a payroll Item column contains one of 3 possible choices. My formula is as follows...
=SUMIFS(Duration:Duration, Payroll Item:Payroll Item, "Hourly",[Payroll Item:Payroll Item, "Overhead Hourly"], [Payroll Item:Payroll Item, "Officer Hourly"])
Not sure what is wrong with this formula or why it will not work.
Thanks!
Best Answer
-
There's a couple things to note here. The first is that whenever you have a column name with a space in it or numbers, you'll need to enclose the name in [these].
For example:
Payroll Item:Payroll Item
Should be:
[Payroll Item]:[Payroll Item]
See: Create a Cell or Column Reference in a Formula
The next thing is that since you're looking for multiple possible options, instead of listing them in a row like that (which means "AND", so it will exclude cells that only contain one option), we'll want to add in an OR statement and use @cell as the range in the OR statement, like so:
[Payroll Item]:[Payroll Item], OR(@cell = "Hourly", @cell = "Overhead Hourly", @cell = "Officer Hourly")
Full formula:
=SUMIFS(Duration:Duration, [Payroll Item]:[Payroll Item], OR(@cell = "Hourly", @cell = "Overhead Hourly", @cell = "Officer Hourly"))
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
-
There's a couple things to note here. The first is that whenever you have a column name with a space in it or numbers, you'll need to enclose the name in [these].
For example:
Payroll Item:Payroll Item
Should be:
[Payroll Item]:[Payroll Item]
See: Create a Cell or Column Reference in a Formula
The next thing is that since you're looking for multiple possible options, instead of listing them in a row like that (which means "AND", so it will exclude cells that only contain one option), we'll want to add in an OR statement and use @cell as the range in the OR statement, like so:
[Payroll Item]:[Payroll Item], OR(@cell = "Hourly", @cell = "Overhead Hourly", @cell = "Officer Hourly")
Full formula:
=SUMIFS(Duration:Duration, [Payroll Item]:[Payroll Item], OR(@cell = "Hourly", @cell = "Overhead Hourly", @cell = "Officer Hourly"))
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
-
Worked like a dream! Thank you!! 😊
-
Wonderful! I'm so glad. Thanks for letting me know 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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!