Do I need a COUNTIF for this scenario?
I'm using the following formula (which works perfectly thanks to this amazing community!) to count the number of distinct installers for a given week. Range 1 is installers Name and Range 2 is a week ending date.
=COUNT(DISTINCT(COLLECT({Field Production Report 2024 Range 1}, {Field Production Report 2024 Range 2}, <=[Week Ending]@row, {Field Production Report 2024 Range 2}, >([Week Ending]@row - 7))))
I have an additional column to designate what kind of install it is, either Res or Com (found in Range 3) and would like to get distinct counts based on this additional criteria. So I thought what if I make this a COUNTIF. I've tried it at the front and tail end but I can't get it to work.
Any suggestions would be greatly appreciated. Thank you...
Best Answer
-
Is this the type of result you are looking for? If so, you can simply add the type of install as an additional condition within the COLLECT statement. These would be your formulas for Res and Com columns:
=COUNT(DISTINCT(COLLECT({Field Production Report 2024 Range 1}, {Field Production Report 2024 Range 2}, <=[Week Ending]@row, {Field Production Report 2024 Range 2}, >([Week Ending]@row - 7), {Range 3}, "Res")))
=COUNT(DISTINCT(COLLECT({Field Production Report 2024 Range 1}, {Field Production Report 2024 Range 2}, <=[Week Ending]@row, {Field Production Report 2024 Range 2}, >([Week Ending]@row - 7), {Range 3}, "Com")))
Answers
-
Is this the type of result you are looking for? If so, you can simply add the type of install as an additional condition within the COLLECT statement. These would be your formulas for Res and Com columns:
=COUNT(DISTINCT(COLLECT({Field Production Report 2024 Range 1}, {Field Production Report 2024 Range 2}, <=[Week Ending]@row, {Field Production Report 2024 Range 2}, >([Week Ending]@row - 7), {Range 3}, "Res")))
=COUNT(DISTINCT(COLLECT({Field Production Report 2024 Range 1}, {Field Production Report 2024 Range 2}, <=[Week Ending]@row, {Field Production Report 2024 Range 2}, >([Week Ending]@row - 7), {Range 3}, "Com")))
-
You would only need to add it to your collect formula. The collect statement only collects the data that matches the criteria included in the statement.
-
@Carson Penticuff, @Hollie Green Thank you for your response.
@Carson Penticuff Thanks for the added clarification, very insightful, not to mention helpful.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!