Average Cost Trend Data by Month

Rick Byers

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)

Packaging Invoices.png

Any help would be appreciated! Thanks.

Bassam Khalil

@Rick Byers 

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

Bassam Khalil

@Rick Byers

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 Khalil

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:

Rick Byers

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

Bassam Khalil

@Rick Byers 

You are welcome and I will be happy to help you any time. Please help the Community by marking it as an ( Accepted Answer)

Rick Byers

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!

Bassam Khalil

@Rick Byers

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

Rick Byers

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

Bassam Khalil

@Rick Byers 

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

Rick Byers

Bassam,

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

Bassam Khalil

@Rick Byers

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

Rick Byers

Bassam,

Thanks so much for your help!

Bassam Khalil

@Rick Byers

At your service any time