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

Cut-off Date
Hello I am looking to create a Smartsheet formula that returns the following result: Column A "Created Date" - this column type is a Created date and show results with date and time Column B "Received Date" - this column would round up the "Created Date" and just show date, no time. If the "Created Date" was after 2:00pm…
Create sequential IDs based on columns responses
Trying to create automatic sequential ID numbers based on the date to ship and where the sample goes. Have a general auto numbering Row ID, have (thanks to reading other questions and responses) figured out how to designate a year. Now I would like to number the Internal (INT) shipments separately from the external…
How to link to data in one sheet and transpose the data in a new shieet
I have a sheet that has data displayed both horizontal and vertical. The primary data is horizontal (date) with information below each date, 4 rows of data. After the data the dates for the next set of data is horizontal with information below it vertical. 1/5/26 1/12/26 1/19/26 1/26/26 JP OS TT LL OS RT RR SS That data…