Referenced Sheet Column Changes

Michelle Maas

My below formula references the Finish date column, {Sales Pipeline Range 5}, and works perfectly. (This column feeds off the Duration column from the date in the Start date column).

=IFERROR(SUMIFS({Sales Pipeline Range 1}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 3}, OR(@cell = "Schedule", @cell = "In Progress", @cell = "Not Started"), {Sales Pipeline Range 5}, AND(MONTH(@cell) = 10, YEAR(@cell) = 2021)), 0)

However, if I change the Finish column to another date column, {Sales Pipeline Range 4}, it now doesn't work. (The dates are typed into this new column or selected from the calendar function in that column.

=IFERROR(SUMIFS({Sales Pipeline Range 1}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 3}, OR(@cell = "Schedule", @cell = "In Progress", @cell = "Not Started"), {Sales Pipeline Range 4}, AND(MONTH(@cell) = 12, YEAR(@cell) = 2021)), 0)

I have done a test row with all columns blank except the four ranges referenced, and then it works. I also tried deleting all the sheet references and starting again, but it does not rectify the Range 4 issue. How can I get this formula to work?

Michelle Maas

Hi @Genevieve P. thanks for getting back to me. I changed the status column to another status column and it is now working.

Genevieve P.

Hi @Michelle Maas

It looks like these formulas don't have the additional IFERRORs around the MONTH and YEAR functions:

=IFERROR(SUMIFS({Sales Pipeline Range 3}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 6}, OR(@cell = "1 - Prospect", @cell = "2 - Quote / Proposal", @cell = "3 - Pending Outcome"), {Sales Pipeline Range 4}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)), 0)

Did that make a difference? If not, what error are you receiving when you take away the IFERROR from the whole thing:

=SUMIFS({Sales Pipeline Range 3}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 6}, OR(@cell = "1 - Prospect", @cell = "2 - Quote / Proposal", @cell = "3 - Pending Outcome"), {Sales Pipeline Range 4}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

Michelle Maas

Trying to troubleshoot the reference formula, I noticed that if I switch the value column from Weighted (formula for quote amount less percentage), to a Value column that is simply a numeric entry (no formula), then Range 4 works. Somehow when I change Range 5 to 4, it no longer picks up the Weighted amount.

Genevieve P.

Hi @Michelle Maas

When you say it doesn't work, are you receiving an error? (What happens if you take the IFERROR away, to see what error it returns?)

With the MONTH and YEAR functions, I generally wrap an individual IFERROR around each of these in case any of the cells in the referenced column contain text or are blank, which would cause them to error.

Try this:

=IFERROR(SUMIFS({Sales Pipeline Range 1}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 3}, OR(@cell = "Schedule", @cell = "In Progress", @cell = "Not Started"), {Sales Pipeline Range 4}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell) = 2021), 0))), 0)

Let me know if this works for you!

Cheers,

Genevieve

Michelle Maas

Hi @Genevieve P. thanks for getting back to me. I changed the status column to another status column and it is now working.

Genevieve P.

I'm glad it's working for you now!

Michelle Maas

Hi @Genevieve P.

I am having issues with this formula / sheet again. Some rows are referencing columns in another sheet for {weighted value} (% calculation), {account name}, {status}, {expected date} and the corresponding row figures are calculating fine.

=IFERROR(SUMIFS({Sales Pipeline Range 3}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 6}, OR(@cell = "1 - Prospect", @cell = "2 - Quote / Proposal", @cell = "3 - Pending Outcome"), {Sales Pipeline Range 4}, AND(MONTH(@cell) = 2, YEAR(@cell) = 2022)), 0)

Yet, other rows will not calculate unless I change the {weighted value} to {forecasted value}. The rows are all together in one section on the main sheet and the formulas are reporting from the same external sheet, but I cannot understand why it works for some {account name} rows and not others.

=IFERROR(SUMIFS({Sales Pipeline Range 1}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 6}, OR(@cell = "1 - Prospect", @cell = "2 - Quote / Proposal", @cell = "3 - Pending Outcome"), {Sales Pipeline Range 4}, AND(MONTH(@cell) = 2, YEAR(@cell) = 2022)), 0)

I need all the formulas to be referencing weighted and expected day. How do I fix this?

Genevieve P.

Hi @Michelle Maas

It looks like these formulas don't have the additional IFERRORs around the MONTH and YEAR functions:

=IFERROR(SUMIFS({Sales Pipeline Range 3}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 6}, OR(@cell = "1 - Prospect", @cell = "2 - Quote / Proposal", @cell = "3 - Pending Outcome"), {Sales Pipeline Range 4}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)), 0)

Did that make a difference? If not, what error are you receiving when you take away the IFERROR from the whole thing:

=SUMIFS({Sales Pipeline Range 3}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 6}, OR(@cell = "1 - Prospect", @cell = "2 - Quote / Proposal", @cell = "3 - Pending Outcome"), {Sales Pipeline Range 4}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

Michelle Maas

Hi @Genevieve P. , thanks for getting back to me. Yes, removing the first IFERROR solved the problem. Thank you so much!

Genevieve P.

Hi @Michelle Maas

That helps, thank you!

The argument set is correct if you used the same syntax as above - I've just tested on my sheet and it works fine. However, is it possible that one of your referenced columns contains an error?

For example, if {Sales Pipeline Range 3} has an error somewhere in one of its cells, then this formula referencing it will also display the same error. Works a bit like dominos!

Are you using a formula in {Sales Pipeline Range 3} on the source sheet?

Genevieve P.

https://community.smartsheet.com/discussion/comment/318536#Comment_318536

Haha I missed the edit to this post! Glad you were able to figure it out.

Cheers,

Genevieve