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
Check out the Formula Handbook template!