# Formula INDEX/COLLECT CLIENT VALUE by MONTH

✭✭✭✭

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

• Overachievers Alumni

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)

• Overachievers Alumni

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.

• Overachievers Alumni

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

• Overachievers Alumni

Great to hear! Glad it's working.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!