SUMIFS Referencing another Sheet

Michael Horton

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

Kelly Moore

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

Kelly Moore

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

Michael Horton

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!!

d_prettyman0414

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!

Genevieve P.

Hi @d_prettyman0414

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