COUNTIFS, within a month AND not blank
I'm really struggling with countifs in my Sheet Summary. I added a helper column to sparse the month. I want to count if the created date is February, and if two columns containing IDs are not blank.
=COUNTIFS([Month]:[Month], = 2,[Contact ID]:[Contact ID 2],""<>)
I'm getting an #UNPARSEABLE error. I tried the formulas as countif individually and they work, but I can't figure out what I'm doing wrong.
Any assistance would be greatly appreciated!
Answers
-
I figured out two of my problems, this formula worked, but please let me know if there's a better way:
=(COUNTIFS([Date Agent Processed]:[Date Agent Processed], IFERROR(MONTH(@cell), 0) = 2, [Additional Calabrio Contact ID]:[Additional Calabrio Contact ID], <>"") + (COUNTIFS([Date Agent Processed]:[Date Agent Processed], IFERROR(MONTH(@cell), 0) = 2, [Calabrio Contact ID]:[Calabrio Contact ID], <>"")))
-
Hi @Y Lazor
You COUNTIFS can hold multiple criteria. Not sure why you would have an IFERROR in the formula. If your objective is to pick up a count of items where Contact ID and Contact ID 2 columns are not blank with the month being 2, you can use this formula.
=COUNTIFS([Month]:[Month], 2, [Additional Calabrio Contact ID]:[Additional Calabrio Contact ID], <> "", [Calabrio Contact ID]:[Calabrio Contact ID], <>"")
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
Hi @AravindGP
It took me a while to get the formula right, but when I did, I realized it was only counting if both ID columns weren't blank. I needed a sum of all non-blank columns.
I read in another forum that countifs will only allow you to include one column per criteria, which was one of my problems. The other issue was I was using ""<> for blank instead of <>"".
Is there a better way to write that formula, than what I came up with above?
I appreciate your assistance!
-
The only thing I would mention would be that you will get a double count on a row if both columns are blank. To avoid this, simply subtract another COUNTIFS counting when both are blank:
=(COUNTIFS([Date Agent Processed]:[Date Agent Processed], IFERROR(MONTH(@cell), 0) = 2, [Additional Calabrio Contact ID]:[Additional Calabrio Contact ID], <>"") + COUNTIFS([Date Agent Processed]:[Date Agent Processed], IFERROR(MONTH(@cell), 0) = 2, [Calabrio Contact ID]:[Calabrio Contact ID], <>"")) - COUNTIFS([Date Agent Processed]:[Date Agent Processed], IFERROR(MONTH(@cell), 0) = 2, [Calabrio Contact ID]:[Calabrio Contact ID], <>"", [Additional Calabrio Contact ID]:[Additional Calabrio Contact ID], <>"")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!