SUMIFS with two criteria
I am trying to SUM up the invoices were the Status is Match by Month and report on another file. I am having trouble with the formula.
=SUMIFS({InvoicebyMonth}, [Invoice Visibility]3, {InvoiceAmt}, {MatchedStatus},"Matched”)
To get the count of invoices I ended up putting in a column to show the number of invoices processed so I could enter a 0 if in Error and the formula below worked.
=SUMIF({InvoicebyMonth}, [Invoice Visibility]1, {Processing}
The intent is for the Monthly Invoice Visibility to be available in a Dashboard but in Real time once the formula is working.
Any help would be appreciated as I am still learning but trying to build a complex dashboard.
Thanks in advance for your help.
Best Answer
-
Found it. You have a set of "smart quotes". Notice in your pasted formula how the last set of quotes is obviously "closing" because of how they are shaped/slanted as opposed to straight up and down like the first quotes and all of the quotes in this comment?
This happens when you type quotes into programs such as Microsoft Word. If you use a program such as Notepad or you type directly into Smartsheet (or here in the Community), you will not get the "smart quotes".
Go into your sheet, delete the last quote, then retype it. See if that works for you. My apologies for not catching that sooner. When I rearranged your original formula, I was just copy/pasting from your post and didn't notice them because I was focused on the syntax.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Let's try a little rearrangement on your first formula...
=SUMIFS({InvoiceAmt}, {InvoicebyMonth}, [Invoice Visibility]3, {MatchedStatus}, "Matched”)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
Thanks for responding. I am getting #UNPARSEABLE. If you have any other ideas, I am open.
This is driving me crazy.
Have a nice evening.
Deb
-
Can you copy/paste the exact formula from your sheet?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Invoice Visibility File Formula
=SUMIFS({InvoiceAmt}, {InvoicebyMonth}, [Invoice Visibility]3, {MatchedStatus}, "Matched”)
Thanks Paul I appreciate your time.
-
Found it. You have a set of "smart quotes". Notice in your pasted formula how the last set of quotes is obviously "closing" because of how they are shaped/slanted as opposed to straight up and down like the first quotes and all of the quotes in this comment?
This happens when you type quotes into programs such as Microsoft Word. If you use a program such as Notepad or you type directly into Smartsheet (or here in the Community), you will not get the "smart quotes".
Go into your sheet, delete the last quote, then retype it. See if that works for you. My apologies for not catching that sooner. When I rearranged your original formula, I was just copy/pasting from your post and didn't notice them because I was focused on the syntax.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
No apologies, I appreciate the help. It worked. You just made my day.
Thanks for all the help.
Have a nice day.
Deb
-
Excellent! Happy to help! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!