SUMIF referencing cells across sheets?

Since reports do not have the capability of summing the content of columns or rows I need to come up with a workaround. For several years I have been doing exactly this in Excel so I want to duplicate in Smartsheet.

In Excel I have created a table that is a list of employees in the rows and a list of dates in the columns. Each cell within the table is a SUMIF formula to grab the total in the tab of the corresponding employee. In Excel the formula looks like this: =SUMIF(Andy!1:1,Table3[[#Headers],[2-Jan Actual]],Andy!29:29). And this is the result:

In an attempt to duplicate in SmartSheet I have created a workspace and each employee has their own sheet. I want a sheet that will grab the total for each week from the appropriate employee's sheet.

Initially I tried to link a cell but the formula would not drag across the columns and it is entirely too much work to link each cell for each employee for all 52 weeks of the year. This is ideal but doesn't seem to work. As an alternative I tried to use a SUMIF formula and receive an error message each time I attempt to click on a new sheet.

HELP!

Excel totals.PNG

Find more posts tagged with

Formulas

Comments

Shaine Greenwood

Hello Heidi,

We currently don't have a way for formulas to directly reference cells in other sheets but I've got your vote down for this on our enhancement request list for further consideration.

If cell linking isn't an option, you'll need to have all of this data on the same sheet and create a SUMIF formula to get the totals you need. Check out our Help Center for details on using formulas in Smartsheet, as Smartsheet formula syntax differs from Excel: https://help.smartsheet.com/topics/formulas-and-functions

Employees can place filters on the sheet to narrow it down to just the data they need to see: https://help.smartsheet.com/articles/504659-using-filters-to-show-or-hide-sheet-data

Otherwise, you can create a report for them: https://help.smartsheet.com/articles/522214-creating-reports

mrisch

Please add my vote to cross-worksheet references. This is a serious deficiency - for this and especially other functions like lookup. It significantly decreases the complexity of the work we can do.

Rrichardson7

Please add my vote to cross-worksheet references.

Katie Williams

Yes, please!!! Need lookup across sheets.

hermes.banico33306

Voting for this issue as well. Very disappointing that this is not included already.

Lucamposleite

Hello Shaine, have been this functionality added up ?

I am looking forward to it !!

Lucamposleite

Hello Shaine, have been this functionality added up ?

I am looking forward to it !!

Joan Bond

Please add my vote, also is this on the roadmap?

J. Craig Williams

Did you see the 2018-02-06 release?

Craig

Shaine Greenwood

Hi Everyone,

You can now reference cells from other sheets in your formulas. More information on this is available in our help center: https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

Joan Bond

I know I can do a sumif across sheets but I've built a report to pull in a number of sheets. Can I do a sumif against a report?

Joan

Shaine Greenwood

Hi Joan,

There currently isn't a way to create formulas for reports, or use reports to build formulas.

When you have a moment, please submit an enhancement request using the form under Quick links on the right of the community site to let our Product team know that you'd like to be able to use reports to build cross-sheet formulas.

t.mazzei0145241

i have been able to create the reference to another sheet and it appears to work now. I have an issue with this and not sure if i am doing something wrong or if its buggy still. I have one sheet that has resources , dates and the hours they have worked on there tasks. the second sheet is the summary. per week per different work class (i.e Project activity, Vacation, Production support ...)

When i look at the first week everything is fine. when a tried to use the SUMIF in the second week i cant change the range without impacting the value of the first cell

Any ideas ?