Welcome!

Help with SUMIF function with data from another sheet

I used this formula to set the range, the criteria and then the sum range but it is returning zero. The range is a different column than the sum range. Any advice?

=SUMIF({SSS Range 3}, >"12/31/2017", {SSS Range 3})

Find more posts tagged with

Formulas

Comments

Chris McKay

Hi Tom,

Even though you've stated the range and sum range are in different columns, it looks like your formula is set to query and sum the same {SSS Range 3} range (which ain't gonna work). Are they definitely different columns/cells?

Tom Holder

yes I tried many times and it still has the same range reference in the formula although the linked arrow(s) in the source sheet are different plus if I click to edit the formula it shows a grey box around the correct data. It could be that I can use 2 different columns?

Chris McKay

OK, I think I understand what's happening here. I've experienced some oddities with the cross-sheet references refusing to let go of previously specified ranges.

To fix:

I'd clear the references and formulas from the target sheet (only those referencing the sources sheet)
save the target sheet
check the source sheet for any stubborn references and clear if necessary
Go back into the target sheet and recreate, making sure to give both ranges unique names

Your formula should be

=SUMIF({The range you want to sum}, "the criteria you want to test", {the range you want to test the criteria against})

I'd also suggest avoiding the >"12/31/2017" criteria (can be unpredictable) and possibly look to validate the current year against the against the query range?

Sign in to join the conversation!

Quick Links

Trending discussions

How to automate data involving dates and sum totals
I have been researching this topic for some time now and have been having trouble finding an answer - hoping the Smartsheet hivemind can help! OBJECTIVE: To obtain a sum total of data submitted via a Smartsheet form, based on submission date. CONTEXT: I work for a transportation company, and we use a Smartsheet form to…
Feedback Needed on Status Rollup Solution
Hi all, I have a bunch of underlying sheets that have the exact same setup due to a template and it has a status field with a drop-down of about half a dozen different options that go sequential order from not started to complete and verified as complete from leadership. We also have hierarchy so there is level 0, 1 and 2…
Automation email row limit issue
We are using Smartsheet for a sign up process with forms and automations. However, automation emails seem to have a limit of 20 rows per email. We need assistance in finding a way to increase this limit or explore alternative solutions.