SUMIFS Referencing another Sheet
Good Evening,
Im currently stuck and trying not to bang my head against a wall. im currently trying to sum the total number of hours based upon 2 sets of criteria. One of the criteria has a name, and the other is within a date range (such as January). I currently have below and it sends back a 0 on the calculation, any help would be greatly appreciated.
The ranges are full columns within another sheet.
Range 3 being the hours column
Range 4 being the employee column
Range 1 is the Day/ Date column
=SUMIFS({HoustonAustin Employee Hours Range 3}, {HoustonAustin Employee Hours Range 4}, "John Doe", {HoustonAustin Employee Hours Range 1}, AND(@cell > DATE(2021, 1, 1), @cell <= DATE(2021, 1, 31)))
Best Answer

Hey @Michael Horton
They syntax of your SUMIFS is correct.
Is {HoustonAustin Employee Hours Range 4} a text/number column, so it does contain 'names' or is it a contact field that contains a mixture of smartsheet contacts and email addresses?
See if this gets you closer
=SUMIFS({HoustonAustin Employee Hours Range 3}, {HoustonAustin Employee Hours Range 4}, FIND("John Doe", @cell)>0, {HoustonAustin Employee Hours Range 1}, AND(@cell > DATE(2021, 1, 1), @cell <= DATE(2021, 1, 31), ISDATE(@cell)))
Unlike HAS and CONTAINS, which returns true/false, the FIND function returns the starting position (the number of characters in) of a string within text. If it finds anything, the value will be greater than zero.
As you continue building formulas in the future, consider renaming the generic smartsheet range number cross sheet references with your actual column names. This will help you and the community better understand your formulas in the future.
Let me know what you get from the above formula and we can continue to troubleshoot until you get your formula working
Kelly
Answers

Hey @Michael Horton
They syntax of your SUMIFS is correct.
Is {HoustonAustin Employee Hours Range 4} a text/number column, so it does contain 'names' or is it a contact field that contains a mixture of smartsheet contacts and email addresses?
See if this gets you closer
=SUMIFS({HoustonAustin Employee Hours Range 3}, {HoustonAustin Employee Hours Range 4}, FIND("John Doe", @cell)>0, {HoustonAustin Employee Hours Range 1}, AND(@cell > DATE(2021, 1, 1), @cell <= DATE(2021, 1, 31), ISDATE(@cell)))
Unlike HAS and CONTAINS, which returns true/false, the FIND function returns the starting position (the number of characters in) of a string within text. If it finds anything, the value will be greater than zero.
As you continue building formulas in the future, consider renaming the generic smartsheet range number cross sheet references with your actual column names. This will help you and the community better understand your formulas in the future.
Let me know what you get from the above formula and we can continue to troubleshoot until you get your formula working
Kelly

Thank you so much!! I ended up finding the issue, the date column i was referencing had properties of a Text/ Number column and not a date column. The minute i changed the properties, everything started working.
I guess its just been an extended Monday!!

Hello! I have a VERY similar issue I cannot figure out. I'm wanting to reference two columns and from the criteria, sum the total. For example, if the first column has a 1 AND is complete in the second column, what is the sum?
My problem lies when I try to reference the sheet, I can't figure out how the syntax appears to have the text just right. So far I have...
=SUMIFS({Hierarchy},{Hierarchy}, 1, [{Status},{Status}, Complete])
I need the formula to search for the 1's in the Hierarchy column that are also complete in the Status column and give me the sum.
Thanks!

This is the structure of a SUMIFS Function:
=SUMIFS({Column to SUM}, {First Column}, "Criteria", {Second Column}, "Criteria")
So in your case, something like this:
=SUMIFS({Total Column}, {Hierarchy}, 1, {Status}, "Complete")
Here are some other resources that you may find helpful:
 Create cross sheet references to work with data in another sheet
 Create a Cell or Column Reference in a Formula
 Formula Foundations Webinar
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 201 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!