Need to Sum everything that meets 2 criteria on a different sheet (Sum INDEX Collect?)
I need help figuring out how to Sum everything that meets 2 criteria on another sheet. My criteria is that if it meets
1) Level AND
2) Cost Code
Calculate the total of hours worked
I used Index Collect to collect the hours worked but don't know how to calculate the total. This is my Formula so far
=INDEX(COLLECT({Units or Hours}, {Level}, Location@row, {Source Cost Code}, [Cost Code]@row), 1)
Best Answer

Hey @NKOH
There are two approaches you can use. Either will work. I would use SUMIFS as this simplifies the number of functions used. SUMIFS does not have any limitations on the number of criteria that can be used  any number is allowed from one to whatever.
=SUMIFS({Units or Hours}, {Level}, Location@row, {Source Cost Code}, [Cost Code]@row)
or
=SUM(COLLECT({Units or Hours}, {Level}, Location@row, {Source Cost Code}, [Cost Code]@row))
Will either of these work for you?
Kelly
Answers

Hey @NKOH
There are two approaches you can use. Either will work. I would use SUMIFS as this simplifies the number of functions used. SUMIFS does not have any limitations on the number of criteria that can be used  any number is allowed from one to whatever.
=SUMIFS({Units or Hours}, {Level}, Location@row, {Source Cost Code}, [Cost Code]@row)
or
=SUM(COLLECT({Units or Hours}, {Level}, Location@row, {Source Cost Code}, [Cost Code]@row))
Will either of these work for you?
Kelly

I went away for 1 hour and figured out Sumifs was my solution!! Thanks
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!