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
-
Try this formula:
=SUMIF(Date:Date, MONTH(@cell) = MONTH(TODAY()), Invoice:Invoice)
I hope this helps.
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.
Answers
-
Try this formula:
=SUMIF(Date:Date, MONTH(@cell) = MONTH(TODAY()), Invoice:Invoice)
I hope this helps.
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.
-
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?
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 382 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!