Formula INDEX/COLLECT CLIENT VALUE by MONTH

09/21/21
Accepted

=INDEX(COLLECT({Sales Pipeline Range 1}, {Sales Pipeline Range 3}, @cell = [Account Name]@row, (SUMF{Sales Pipeline Range 6}, MONTH(@cell) = 1))

I want a formula that recognises on a referenced sheet a client name, and totals the value by month.

On the reporting sheet I have a table with a column for [Account Name], which matches a referenced sheet's column, {Client} = {Sales Pipeline Range 3}.

Then from the same referenced sheet, I want it to pick up the [Sales Value} = {Sales Pipeline Range 1} for say January, and feed it into the reporting sheet's column for January, and calculate the total if there are different sales quotes for that same client/month.

The above formula is showing error #UNPARSEABLE. What is not right?

Best Answer

  • David TutwilerDavid Tutwiler Overachievers
    Accepted Answer

    You could wrap the whole thing in an IFERROR and tell it what you want to display if there is an error (I'm assuming 0). Something like this should get you there:

    =IFERROR(SUMIFS({Sales Pipeline Range 1}, {Sales Pipeline Range 3}, [Account Name]@row, {Sales Pipeline Range 6}, MONTH(@cell) = 1), 0)

Answers

  • David TutwilerDavid Tutwiler Overachievers

    I think you're looking for a SUMIFS formula. Something like this should work:

    =SUMIFS({Sales Pipeline Range 1}, {Sales Pipeline Range 3}, [Account Name]@row, {Sales Pipeline Range 6}, MONTH(@cell) = 1)

    Also, have you looked at the Pivot App? It seems like it may be a good fit to do this for you automatically.

  • That worked great, thank you @David Tutwiler. If the sheet did not have a matching account name, how would I include that in the formula? These are showing error as #INVALID DATA.

  • =SUMIFS(NOT(ISBLANK({Sales Pipeline Range 1}, {Sales Pipeline Range 3}, [Account Name]@row, {Sales Pipeline Range 6}, MONTH(@cell) = 1)))

    I'm trying the above but it's not reading right, error #INCORRECT ARGUMENT SET.

  • David TutwilerDavid Tutwiler Overachievers
    Accepted Answer

    You could wrap the whole thing in an IFERROR and tell it what you want to display if there is an error (I'm assuming 0). Something like this should get you there:

    =IFERROR(SUMIFS({Sales Pipeline Range 1}, {Sales Pipeline Range 3}, [Account Name]@row, {Sales Pipeline Range 6}, MONTH(@cell) = 1), 0)

  • Excellent, that worked perfectly. Thank you @David Tutwiler

  • David TutwilerDavid Tutwiler Overachievers

    Great to hear! Glad it's working.

Sign In or Register to comment.