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

Find more posts tagged with

Accepted answers

Ramzi K

@Derek Meldrum

Try this formula:

=SUMIF(Date:Date, MONTH(@cell) = MONTH(TODAY()), Invoice:Invoice)

I hope this helps.

Cheers,

Ramzi

All comments

Ramzi K

@Derek Meldrum

Try this formula:

=SUMIF(Date:Date, MONTH(@cell) = MONTH(TODAY()), Invoice:Invoice)

I hope this helps.

Cheers,

Ramzi

Derek Meldrum

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

@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

Derek Meldrum

@Ramzi K

thanks for the reply.

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

Decall95

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.