Formula to sum a column from a 2nd sheet with 1:many match across 2 sheets

Lisa Matthews

Hello, I am stumped with a cross-sheet formula. I tried the AI tool but only received links to articles on cross-sheet linking, so I'm reaching out to the community for a life line. This is what I am trying to calculate:

Sum [Est. Cost] from sheet: {Construction Estimates Details} only if the values in [Task Name] column matches in both sheets: sheet 1: {Suites 100 & 110 Expansion Plan}, sheet 2: {Construction Estimates Details}. One:Many, I can't figure out.

Here is an image of what I am trying to do:

Thank you for your help, in advance.

-Lisa

AKnight

Hi @Lisa Matthews!

Did you check the spelling in your task columns? I noticed "Architectural" has a couple different spelling options in your second sheet. Once that is fixed it should sum all values using the same spelling.

AKnight

Hi Lisa!

Could you try =SUMIF({Task Name Sheet 2}, @cell = [Task Name]@row, {Construction Estimates Details})

With a new reference that is over the "Task Name" column in the Construction Estimates Details sheet.

Lisa Matthews

Hi @AKnight , thank you. I tried this formula and get an #UNPARSEABLE result.

AKnight

Sorry I think you have to remove the @cell = part because it is a cross sheet reference. Can you try the formula below instead?

=SUMIF({Task Name Sheet 2}, [Task Name]@row, {Construction Estimates Details})

Lisa Matthews

@AKnight I used the updated formula. "#INVALID REF" appears in the cell

Paul Newcome

The @cell can still be used in cross sheet references. In fact, I always suggest it as a best practice for a couple of different reasons.

The #INVALID REF error comes from a {Cross Sheet Reference} that has not yet been set up. Ensure you have followed the appropriate steps for creating both of your cross sheet references.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

.

Lisa Matthews

@AKnight @Paul Newcome

Thank you. I updated the formula to:

=SUMIF({Construction Estimates Details Range 1}, [Task Name]@row, {Construction Estimates Details Range 2})

No error message, however, it is only returning the first matched amount, not calculating the sum. Not sure what I am doing wrong with the formula. Thank you for your help.

AKnight

Hi @Lisa Matthews!

Did you check the spelling in your task columns? I noticed "Architectural" has a couple different spelling options in your second sheet. Once that is fixed it should sum all values using the same spelling.

Lisa Matthews

Hi @AKnight ! I updated spelling and set the formula for the whole column and it is calculating correctly for each row! Working perfectly! Thank you soooo much for your help!!! I appreciate you!

Happy Friday!