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.

Find more posts tagged with

Education

Formulas

Dashboards

Sheet Summary

Accepted answers

All comments

Razetto

@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")))

Serenidy

@Razetto When I try that I just get 0

Razetto

@Serenidy Maybe 0 is the right answer(?).

Serenidy

@Razetto I cross reference it to our Excel pivot tables and the answer is supposed to be close to 128

Razetto

@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")))

Serenidy

@Razetto No its #unparseable now

Razetto

@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)))

Serenidy

@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)))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.