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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @Rick Byers 

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

    PMP Certified

    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 Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @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


    PMP Certified

    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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 06/15/21

    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:


    PMP Certified

    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"

  • Rick Byers
    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
    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)

    PMP Certified

    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"

  • Rick Byers
    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
    Bassam Khalil ✭✭✭✭✭✭
    edited 06/16/21

    @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

    PMP Certified

    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"

  • Rick Byers
    Rick Byers ✭✭✭✭

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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @Rick Byers 

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

    PMP Certified

    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"

  • Rick Byers
    Rick Byers ✭✭✭✭

    Bassam,

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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    @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


    PMP Certified

    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"

  • Rick Byers
    Rick Byers ✭✭✭✭

    Bassam,

    Thanks so much for your help!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Rick Byers

    At your service any time

    PMP Certified

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!