Count if distinct and if meets set criteria
I am trying to count unique rows if certain information is met.
For example, one of the formulas I have written is =COUNTIFS({PledgeAmountSummary2022 Range 1}, Campaign@row, {PledgeAmountSummary2022 Range 4}, Division@row). This works until I try to add the stipulation of only counting unique data based on another column within the same reference sheet.
Can someone help me set this formula up correctly? I have tried a number of times and a number of ways and keep getting errors.
Best Answer
-
This formula counts unique dates in the column Schedule date that meet the condition of being older (les than) today. (change the row numbers to @row to expand it)
=COUNT(DISTINCT(COLLECT([Schedule date]39:[Schedule date]47, [Schedule date]39:[Schedule date]47, <TODAY())))
You should be able to use those functions within your formula.
dm
Answers
-
This formula counts unique dates in the column Schedule date that meet the condition of being older (les than) today. (change the row numbers to @row to expand it)
=COUNT(DISTINCT(COLLECT([Schedule date]39:[Schedule date]47, [Schedule date]39:[Schedule date]47, <TODAY())))
You should be able to use those functions within your formula.
dm
-
@Dale Murphy, Thank you but I am not looking at a date. I am trying to count the number of unique donations for each location and each campaign that we have.
-
The principle should apply. Instead of doing a distinct collect on date, use whatever is unique about the donation row. (Is there an id or registration number of some kind?)
dm
-
That worked! Thank you @Dale Murphy
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!