I wish to count column A with a fixed value, with a unique date in Column B
Column A Column B
Smith 5/27/21
Jones 5/27/21
Smith 5/29/21
Smith 5/27/21
I want to count column A with "Smith" and a unique date in Column B
Best Answer
-
Hi @Art Davida
You can use a DISTINCT(COLLECT formula for this! The COLLECT function finds all the rows with a specific person, then the DISTINCT checks through the dates for unique dates. Then wrap this in a COUNT function and you should receive the number you're looking for.
Try this:
=COUNT(DISTINCT(COLLECT([Column B]:[Column B], [Column A]:[Column A], [Column A]@row)))
You would apply this to an entire column. However, if you're looking to have this just once, like in a Sheet Summary formula, then you can identify the criteria as data "in quotes" instead of saying [Column A]@row.
=COUNT(DISTINCT(COLLECT([Column B]:[Column B], [Column A]:[Column A], "Smith")))
Let me know if this works for you or if you'd like to see screen capture examples.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
correct answer for the above is 2
i.e. there are two rows of Smith with unique dates
-
Hi @Art Davida
You can use a DISTINCT(COLLECT formula for this! The COLLECT function finds all the rows with a specific person, then the DISTINCT checks through the dates for unique dates. Then wrap this in a COUNT function and you should receive the number you're looking for.
Try this:
=COUNT(DISTINCT(COLLECT([Column B]:[Column B], [Column A]:[Column A], [Column A]@row)))
You would apply this to an entire column. However, if you're looking to have this just once, like in a Sheet Summary formula, then you can identify the criteria as data "in quotes" instead of saying [Column A]@row.
=COUNT(DISTINCT(COLLECT([Column B]:[Column B], [Column A]:[Column A], "Smith")))
Let me know if this works for you or if you'd like to see screen capture examples.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
It sure did work. The function nesting wasn't entirely intuitive, but can't argue with results. Thank you very much
-
No problem! Thanks for letting me know.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!