Cross Sheet SUMIFS #unparseable

Bryan Moss

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:

Kelly Moore

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")

Kelly Moore

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

Kelly Moore

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")

Bryan Moss

That was the tweak needed! Thank you!

Bryan Moss

@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

Kelly Moore

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

Bryan Moss

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)

Kelly Moore

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