Sumif formula for current month invoices

Options
Derek Meldrum
Derek Meldrum ✭✭✭
in Formulas and Functions

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

· Share on FacebookShare on Twitter

Best Answer

Answers

  • Derek Meldrum
    Derek Meldrum ✭✭✭
    Options

    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?

    · Share on FacebookShare on Twitter
  • Ramzi K
    Ramzi K ✭✭✭✭✭
    Options

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

    · Share on FacebookShare on Twitter
  • Derek Meldrum
    Derek Meldrum ✭✭✭
    Options

    @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

    · Share on FacebookShare on Twitter
  • Decall95
    Decall95 ✭✭
    Options
    https://community.smartsheet.com/discussion/comment/265108#Comment_265108

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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!