# Average Cost Trend Data by Month

Options
✭✭✭✭

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.

• ✭✭✭✭✭✭
Options

I will check it and do my best to create this formula for you ASAP.

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 06/15/21
Options

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

• ✭✭✭✭
Options

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))

• ✭✭✭✭✭✭
Options

bassam.khalil2009@gmail.com

• ✭✭✭✭
Options

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!

• ✭✭✭✭✭✭
edited 06/16/21
Options

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

• ✭✭✭✭
Options

Great idea. I just shared the 2 sheets that I am using for this project. Thanks!

• ✭✭✭✭✭✭
Options

I will check it and do my best to create this formula for you ASAP.

bassam.khalil2009@gmail.com

• ✭✭✭✭
Options

Bassam,

Thanks so much for your help! The formula works exactly as I had hoped!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

Bassam,

Thanks so much for your help!

• ✭✭✭✭✭✭
Options