Sumif formula for current month invoices

Hi,  I am relatively new to smart sheet and struggling to pin down the correct formula for this one. I am looking to calculate the total value of invoices within the current month throughout the year to allow me to highlight on the dashboard.

So far the best I can come up with is

=SUMIF([Invoiced Date]:[Invoiced Date], = MONTH(TODAY()), [Pro Forma Invoice Value]:[Pro Forma Invoice Value]))

However this returns unparsable value.

Additionally our sheet allows to highlight rows planned for invoice in a particular month by selecting a month from drop down list.  id like to use this to calculate planned invoicing to highlight a figure in widget also.

Any help would be appreciated

Best Answer

Answers

  • That is excellent, thankyou, worked perfectly.

    Would you be able to assist with another?


    I have columns which indicate if a task is likely to be invoice that month by selecting the month ie "January" from a preset drop down menu and I sum the value of each quoted value column for each month using basic sumif. storing in helper columns.  How can I highlight in a dashboard widget only the current month items?

  • Ramzi K
    Ramzi K ✭✭✭✭✭

    @Derek Meldrum

    One way to do it is using the Sheet Summary area. Create a field in the Sheet Summary and have the formula there sum your invoice column by invoices that will be paid in =SUMIF(MONTH(Date:Date), 1) for January, 2 for Feb, etc.

    Then use the field as a widget in your Dashboard.

    I hope that makes sense.

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • @Ramzi K


    thanks for the reply.

    Im

    I’m not sure how I would formula to specifically show only current month.

    the drop down month Column is single select of particular month ie “January”

    currently I have each month totalling separately in helper column.

    can you help with only highlighting only the sum which falls under current month?

    essentially I am looking to highlight invoice value to date ( as per the initial formula you assisted with) and then the total value of planned invoicing.

    thanks

  • I keep getting an INVALID DATA TYPE ERROR when using the formula format mentioned above - any idea why that might be the case?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!