Average Cost Trend Data by Month
I am trying to collect month by month average cost trend data using the average monthly Invoice Total for Body (Packaging Description) and Plates (Invoice Description). Honestly, I have tried several different formulas, but I really could use some help in writing the formula. The Packaging Invoices sheet will continue to have data added to it, so I'd like this to always upload data to my data collection sheet, created to collect the month by month average costs, so I can create a chart. I am attaching a partial screenshot of my current sheet.
Here's where I am at with my formula, but, it is returning an #Unparseable warning.
=AVG({Packaging Invoices 2021 Range 2}, {Packaging Invoices 2021 Range 5}, "Body", {Packaging Invoices 2021 Range 3}, "Plates"), COUNTIF([Date]:[Date], IFERROR(MONTH(@cell), 0) = 1)
Any help would be appreciated! Thanks.
Best Answers
-
I will check it and do my best to create this formula for you ASAP.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
You are welcome and I will be happy to help you any time. and for the Smartsheet Community please find in the following the final solution:
=IFERROR(SUMIFS({Packaging Invoices 2021-Invoice Total}, {Packaging Invoices 2021-Packaging Description}, "Body", {Packaging Invoices 2021-Invoice Discription}, "Plates", {Packaging Invoices 2021-Date}, IFERROR(MONTH(@cell), "") = 4) / COUNTIFS({Packaging Invoices 2021-Packaging Description}, "Body", {Packaging Invoices 2021-Invoice Discription}, "Plates", {Packaging Invoices 2021-Date}, IFERROR(MONTH(@cell), "") = 1), "")
Date Sheet
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers
-
Hi @Rick Byers
Hope you are fine, i created your formula in the same sheet and you can change it to use reference from different sheet, please try the following formula:
=SUMIFS([INVOICE TOTAL]:[INVOICE TOTAL], [PACKAGING DESCRIPTION]:[PACKAGING DESCRIPTION], "Body", [INVOICE DESCRIPTION]:[INVOICE DESCRIPTION], "Plates", Date:Date, IFERROR(MONTH(@cell), "") = 1) / (COUNTIFS([PACKAGING DESCRIPTION]:[PACKAGING DESCRIPTION], "Body", [INVOICE DESCRIPTION]:[INVOICE DESCRIPTION], "Plates", Date:Date, IFERROR(MONTH(@cell), "") = 1))
the following screenshot shows the result:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Bassam,
Thanks for your help! As far as placing the reference sheets into the formula, I would think I need to add them in 3 places, before: "Invoice Total", "Packaging Description" and "Invoice Description", so the formula knows what columns to pull information, correct? My reference sheet is titled "Packaging Invoices 2021". Here's where I am but not getting a result:
=SUMIFS({Packaging Invoices 2021 Range 2}, [INVOICE TOTAL]:[INVOICE TOTAL], {Packaging Invoices 2021 Range 5}, [PACKAGING DESCRIPTION]:[PACKAGING DESCRIPTION], "Body", {Packaging Invoices 2021 Range 3}, [INVOICE DESCRIPTION]:[INVOICE DESCRIPTION], "Plates", {Packaging Invoices 2021 Range 1}, Date:Date, IFERROR(MONTH(@cell), "") = 1) / (COUNTIFS([PACKAGING DESCRIPTION]:[PACKAGING DESCRIPTION], "Body", [INVOICE DESCRIPTION]:[INVOICE DESCRIPTION], "Plates", Date:Date, IFERROR(MONTH(@cell), "") = 1))
-
You are welcome and I will be happy to help you any time. Please help the Community by marking it as an ( Accepted Answer)
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Hi Bassam,
As per my previous comment, I am not able to get the formula to work properly, so I am still needing help on this. See above for where I am with the formula. Thanks!
-
If you can share me as an admin on a sample copy of your sheet (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.
My Email : Bassam.k@mobilproject.it
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Great idea. I just shared the 2 sheets that I am using for this project. Thanks!
-
I will check it and do my best to create this formula for you ASAP.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Bassam,
Thanks so much for your help! The formula works exactly as I had hoped!
-
You are welcome and I will be happy to help you any time. and for the Smartsheet Community please find in the following the final solution:
=IFERROR(SUMIFS({Packaging Invoices 2021-Invoice Total}, {Packaging Invoices 2021-Packaging Description}, "Body", {Packaging Invoices 2021-Invoice Discription}, "Plates", {Packaging Invoices 2021-Date}, IFERROR(MONTH(@cell), "") = 4) / COUNTIFS({Packaging Invoices 2021-Packaging Description}, "Body", {Packaging Invoices 2021-Invoice Discription}, "Plates", {Packaging Invoices 2021-Date}, IFERROR(MONTH(@cell), "") = 1), "")
Date Sheet
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Bassam,
Thanks so much for your help!
-
At your service any time
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!