Count based on two criteria
Hi,
This is just an example below. I am trying to get a formula for the 'count' column. I want to return a number from 'dates' and 'group'. If there are two of the same dates with the same group, I want a '2' in that cell. If there is only one date for that group, I want a '1' in that cell. Does that make sense?
TIA
Best Answer
-
Hi @JJMunoz
the @row is a function that needs to be typed out in lower case. Try:
=COUNTIFS([PARTURITION DATE]:[PARTURITION DATE], [PARTURITION DATE]@row, COMPOUND:COMPOUND, COMPOUND@row)
See: Create Efficient Formulas with @cell and @row
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
@JJMunoz Try this:
=COUNTIFS(date:date, date@row, group:group, group@row)
This says count the rows where the date is the same as this row and the group is the same as this row.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks- I tried that and #unparseable is coming up.
-
Hi @JJMunoz
In Jeff's example, the column names would need to be the same as your column names. Your screen capture has the text "Date" and "Group" in a cell, so the formula is:
=COUNTIFS(date:date, date@row, group:group, group@row)
However if the column that houses your date is actually called "Start Date", then you'd need to update it to:
=COUNTIFS([Start Date]:[Start Date], [Start Date]@row, group:group, group@row)
Does that make sense? Here's an article that goes through referencing columns in formulas: Create a Cell or Column Reference in a Formula
If this hasn't helped, it would be useful to see a screen capture of the actual sheet including column names and identifying column types, but please block out sensitive data.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks for jumping in, Genevieve.
@JJMunoz it always helps to provide screenshots of column names, data layout, etc and include any formulas you have tried and the resulting error messages. With this info, most of the time other users or the real experts in this community can get you pointed in the right direction pretty quickly.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman @Genevieve P. Thank you both. What is still wrong?
Here is a screenshot. This is the current formula in the cell.
=COUNTIFS([PARTURITION DATE]:[PARTURITION DATE], [PARTURITION DATE]@ROW, COMPOUND:COMPOUND, COMPOUND@ROW)
-
Hi @JJMunoz
the @row is a function that needs to be typed out in lower case. Try:
=COUNTIFS([PARTURITION DATE]:[PARTURITION DATE], [PARTURITION DATE]@row, COMPOUND:COMPOUND, COMPOUND@row)
See: Create Efficient Formulas with @cell and @row
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. That was it! Thank you.. I'm new to this, so I appreciate the help!
-
No problem! We've all been new to this at some point 🙂
That's where the Community is great - there are so many wonderful Smartsheet users like @Jeff Reisman who can help answer questions!
I would also recommend the Smartsheet University if your plan has access to it, and the free Onboarding Webinars, here.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
The two links in my signature below go to the Functions List (which links to all their help pages) and to the Formula Error Messages list. Both are invaluable resources for working with Smartsheet formulas, I use them just about every day.
Another very helpful resource can be found in the Solution Center. Search for "formula examples" and you'll find a template you can add to your Smartsheet folders. This template contains real examples for all the functions, beyond what you get on the Function help pages.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!