Need to Sum everything that meets 2 criteria on a different sheet (Sum INDEX Collect?)

NKOH

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)

Kelly Moore

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

https://help.smartsheet.com/function/sumifs

Kelly Moore

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

https://help.smartsheet.com/function/sumifs

NKOH

I went away for 1 hour and figured out Sumifs was my solution!! Thanks