How to Setup COUNT/COUNTIFS formulas
I manage the educational benefits at my job and am trying to find totals of how many dependents, drivers, and office employees participate in the benefit.
This is how I've been setting up my formulas. Yet when we cross reference with Excel the total number is off.
=COUNT(DISTINCT(COLLECT([Employee ID/Driver Code]:[Employee ID/Driver Code], [Reimbursement or Debt Free]:[Reimbursement or Debt Free], "Debt Free", Company:Company, "Knight Transportation", [Reimb. Year]:[Reimb. Year], "2023")))
=COUNT(DISTINCT(COLLECT(Created:Created, [Reimbursement or Debt Free]:[Reimbursement or Debt Free], "Debt Free", Company:Company, "Knight Transportation", [Driver or Non-Driver]:[Driver or Non-Driver], "I'm an office/shop employee")))
I'm not sure if there is another way to find the totals or if I'm just setting them up incorrectly.
Answers
-
@Serenidy Have you tried adding the @cell reference before? =COUNT(DISTINCT(COLLECT([Employee ID/Driver Code]:[Employee ID/Driver Code], [Reimbursement or Debt Free]:[Reimbursement or Debt Free], @cell="Debt Free", Company:Company, @cell="Knight Transportation", [Reimb. Year]:[Reimb. Year], @cell="2023")))
-
@Razetto When I try that I just get 0
-
@Serenidy Maybe 0 is the right answer(?).
-
@Razetto I cross reference it to our Excel pivot tables and the answer is supposed to be close to 128
-
@Serenidy I meant to write the formula as COUNTIFS...does it make any difference? COUNTIFS([Reimbursement or Debt Free]:[Reimbursement or Debt Free], @cell="Debt Free", Company:Company, @cell="Knight Transportation", [Reimb. Year]:[Reimb. Year], @cell="2023")))
-
@Razetto No its #unparseable now
-
@Serenidy I've removed the quotation marks the year had. See if that works. =COUNT(DISTINCT(COLLECT([Employee ID/Driver Code]:[Employee ID/Driver Code], [Reimbursement or Debt Free]:[Reimbursement or Debt Free], @cell="Debt Free", Company:Company, @cell="Knight Transportation", [Reimb. Year]:[Reimb. Year], @cell=2023)))
-
@Razetto That worked! For formulas with more criteria would I keep adding the @cell?
This formula gave me 0. What is incorrect?
=COUNT(DISTINCT(COLLECT([Employee ID/Driver Code]:[Employee ID/Driver Code], [Driver or Non-Driver]:[Driver or Non-Driver], @cell = "I'm a dependent eligible for the benefit", [Reimbursement or Debt Free]:[Reimbursement or Debt Free], @cell = "Debt Free", [Name of College (enter name if not in list)]:[Name of College (enter name if not in list)], @cell = "CTU - COLORADO TECHNICAL UNIVERSITY", Company:Company, @cell = "Knight Transportation", [Reimb. Year]:[Reimb. Year], @cell = 2023)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!