Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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?

  • ✭✭✭✭✭

    @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!

Trending in Formulas and Functions