Formula INDEX/COLLECT CLIENT VALUE by MONTH

Michelle Maas · 09/21/21

=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?

David Tutwiler · 09/22/21

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)

David Tutwiler · 09/21/21

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.

Michelle Maas · 09/22/21

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.

Michelle Maas · 09/22/21

=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 Tutwiler · 09/22/21

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)

Michelle Maas · 09/22/21

Excellent, that worked perfectly. Thank you @David Tutwiler

David Tutwiler · 09/22/21

Great to hear! Glad it's working.

Formula INDEX/COLLECT CLIENT VALUE by MONTH

Best Answer

Answers

Help Article Resources

Categories