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

COUNTIFS referencing another sheet
Hello, Using the AI tool I generated a COUNTIFS formula to count the number of rows that have specific text in two different columns. However, despite the fact that the criteria is met, the formula returns "0". What am I doing wrong?
Counting value repeats since last distinct value
Hi Smartsheet experts! I'm trying to accomplish something like the above, where I count the number of repeats of one value in a column, but that count resets when the value changes and resets to 1 if the same name appears later. This is to consolidate an order form where users can order different numbers of items- it's…
Total Sum not calculating in Smartsheet correctly
Hello SmartSheet Community. I have created a report with various categories and totals. I wanted to capture certain categories and their totals however the totals are not calculating correctly. I would have to copy the totals, paste them to an excel blank sheet and AutoSum there. Can someone see what I am doing wrong?…