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?
Best 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
-
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.
-
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
-
Great to hear! Glad it's working.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 285 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!