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

Calculate number of days between two dates in the same column on the same sheet
Hello, Can a combination of the INDEX(COLLECT and NETDAYS Formula be used to get the number of days between two dates in the same column? I want to get the number of days between two dates on the same sheet, in the same column, that have matching names and different project types. For Example: Studio Name is equal to…
Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…