Referenced Sheet Column Changes

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))

All comments

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

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!

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.

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:

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

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?