Cross Sheet SUMIFS #unparseable
Hello Community,
I am trying to get a cross sheet SUMIFS working and I am stuck on something in the syntax. I can get the exact SUMIFS to work within a sheet but when I try to create the SUMIFS on a summary sheet I get #unparseable. I am sure it is right in front of my face but I can't seem to fix it and I haven't found any others in the forums with this issue.
This version works within source data sheet. The Resource1 in this formula is for my testing and it is a cell I entered to build the formula but I want it to ultimately use cell on the summary sheet in the version below.
=SUMIFS(Hours:Hours, Resource:Resource, CONTAINS($Resource$1, @cell), Billable:Billable, "Yes")
This is the version I have in the summary sheet and it is referencing the Resource Name on the summary sheet as desired but the formula is not working.
=SUMIFS({Project Time Tracking Range 2}, {Project Time Tracking Range 3}, CONTAINS([Resource Name]@row), {Project Time Tracking Range 4}, Yes@row)
Goal: I want a formula on the summary sheet that sums the hours column in the source data sheet if it is coded as billable=yes and if the name matches a cell in the summary sheet on the same line as the formula.
What am I missing? Any help would be greatly appreciated!
Summary sheet example
Source sheet example:
Best Answers
-
Hey @Bryan M.
I think there's only small tweaks needed.
=SUMIFS({Project Time Tracking Range 2}, {Project Time Tracking Range 3}, CONTAINS([Resource Name]@row, @cell), {Project Time Tracking Range 4}, "Yes")
-
Hey @Bryan M.
Do you need to use CONTAINS? If the value in the source sheet exactly matches the target row, we can directly insert that reference without CONTAINS.
If you must use CONTAINS, Is the Resource column a contact column, or is it just containing a name like John Smith - but not as a contact? If the field is a contact and you MUST use a function, then we have to use FIND. FIND returns a number (a position number) instead of true, so if we use FIND in this capacity we must look for FIND()>0, which means it found something.
Also, as a good practice, change the default name of your ranges to reflect the column they are referencing. I typically leave the sheet name and overwrite the 'range #' portion. This allows me to look at a formula and understand exactly what is being pulled in.
First, try this one (does it have to be [Resource Name]$2 or is it [Resource Name]@row ?)
=SUMIFS({Project Time Tracking Range 2}, {Project Time Tracking Range 3}, [Resource Name]$2, {Project Time Tracking Range 4}, "Approved", {Project Time Tracking Range 3}, [Calendar Week]@row)
If you must use a CONTAINS type function, try this
=SUMIFS({Project Time Tracking Range 2}, {Project Time Tracking Range 3}, FIND([Resource Name]$2, @cell)>0, {Project Time Tracking Range 4}, "Approved", {Project Time Tracking Range 3}, [Calendar Week]@row, @cell)
Kelly
Answers
-
Hey @Bryan M.
I think there's only small tweaks needed.
=SUMIFS({Project Time Tracking Range 2}, {Project Time Tracking Range 3}, CONTAINS([Resource Name]@row, @cell), {Project Time Tracking Range 4}, "Yes")
-
That was the tweak needed! Thank you!
-
@KDM You nailed the last one, any thoughts on what I am missing here? I keep getting 0 as result when it should be returning a value? I have tried changing order of my criteria a handful of items but I can't seem to find the miss. Trying to sum by resource (fixed cell reference) by calendar week (@row reference, @cell search) by "approved" so that I can see how many hours each resource had per week vs the total hours per week. Any ideas?
This is returning 0 =SUMIFS({Project Time Tracking Range 2}, {Project Time Tracking Range 3}, CONTAINS([Resource Name]$2, @cell), {Project Time Tracking Range 4}, "Approved", {Project Time Tracking Range 3}, CONTAINS([Calendar Week]@row, @cell))
Range2= Hours
Range3= Resource
Range4= Approved Status
-
Hey @Bryan M.
Do you need to use CONTAINS? If the value in the source sheet exactly matches the target row, we can directly insert that reference without CONTAINS.
If you must use CONTAINS, Is the Resource column a contact column, or is it just containing a name like John Smith - but not as a contact? If the field is a contact and you MUST use a function, then we have to use FIND. FIND returns a number (a position number) instead of true, so if we use FIND in this capacity we must look for FIND()>0, which means it found something.
Also, as a good practice, change the default name of your ranges to reflect the column they are referencing. I typically leave the sheet name and overwrite the 'range #' portion. This allows me to look at a formula and understand exactly what is being pulled in.
First, try this one (does it have to be [Resource Name]$2 or is it [Resource Name]@row ?)
=SUMIFS({Project Time Tracking Range 2}, {Project Time Tracking Range 3}, [Resource Name]$2, {Project Time Tracking Range 4}, "Approved", {Project Time Tracking Range 3}, [Calendar Week]@row)
If you must use a CONTAINS type function, try this
=SUMIFS({Project Time Tracking Range 2}, {Project Time Tracking Range 3}, FIND([Resource Name]$2, @cell)>0, {Project Time Tracking Range 4}, "Approved", {Project Time Tracking Range 3}, [Calendar Week]@row, @cell)
Kelly
-
Thank you again @KDM! I had to tweak my resource match column to be exact names of my source sheet. My source sheet has the resource name + employeeID appended in a non-diliminated cell and I am trying to avoid having to make any data adjustments on the source sheet. Also renaming my references as you suggested really helped.
Names mapped to hard reference$ since it is one small block of names in a column and I want to run it down a column in the same sheet to get full 52 weeks of hours*resource*week. It blocks me from using a cell formula with the cell$ but it is now working and returning accurate results.
I don't think I added the (1-52) after Calendar Week so I suspect it is the way the formula completed/corrected syntax.
Here is the final working version. Thank you!
=SUMIFS({Project Time Tracking Hours}, {Project Time Tracking Resource}, [Resource Name]$2, {Project Time Tracking Approved_Status}, "Approved", {Project Time Tracking Range Work_Week}, [Calendar Week (1-52)]@row)
-
Glad you got it to work. I see I accidentally forgot to delete an @cell at the end of the SumIfs in my formula. Whoops.
Glad you didn’t have to separate your joined name- if you ever have to do it hopefully the employee ID part is a fixed length so that you could use the RIGHT function
cheers
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!