Referenced Sheet Column Changes
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?
Best Answers
-
Hi @Genevieve P. thanks for getting back to me. I changed the status column to another status column and it is now working.
-
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))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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.
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Genevieve P. thanks for getting back to me. I changed the status column to another status column and it is now working.
-
I'm glad it's working for you now!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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?
-
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))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Genevieve P. , thanks for getting back to me. Yes, removing the first IFERROR solved the problem. Thank you so much!
-
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?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Haha I missed the edit to this post! Glad you were able to figure it out.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!