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

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?