Average Cost Trend Data by Month

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

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

All comments

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,

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)

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

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,

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:

Date Sheet

Bassam,

Thanks so much for your help!