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({Houston-Austin Employee Hours Range 3}, {Houston-Austin Employee Hours Range 4}, "John Doe", {Houston-Austin 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 {Houston-Austin 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({Houston-Austin Employee Hours Range 3}, {Houston-Austin Employee Hours Range 4}, FIND("John Doe", @cell)>0, {Houston-Austin 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 {Houston-Austin 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({Houston-Austin Employee Hours Range 3}, {Houston-Austin Employee Hours Range 4}, FIND("John Doe", @cell)>0, {Houston-Austin 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
Help Article Resources
Categories
Check out the Formula Handbook template!
