CountIf And

Options

I am looking to count how many audit types, "DD", did my auditor "Brian" do. I am referencing and different sheet.

=COUNTIF({MB Intake Audits - 2024 Type of Audit}, "DD", AND({MB Intake Audits - 2024 Auditor Name}, "Brian"))

Tags:

Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Options

    Try:

    =COUNTIFS({MB Intake Audits - 2024 Type of Audit}, "DD", {MB Intake Audits - 2024 Auditor Name}, "Brian"

  • Joey135
    Joey135 ✭✭✭✭
    Options

    Well that kinda worked @Nic Larsen. It returned 0 when I know I have DDs and Brian in those columns.

  • Joey135
    Joey135 ✭✭✭✭
    Options

    I tried this too.

    =COUNTIF({MB Intake Audits - 2024 Type of Audit}, "DD", CONTAINS(“Brian”, {MB Intake Audits - 2024 Auditor Name})

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Options

    Things that come to mind - Are your cross-sheet ranges set to the entire column? Is your Auditor Name column set up as contact list or just a text/number? Is there more than one name listed in the Auditor Name column?

    I would use a COUNTIFS over a COUNTIF formula. You can also test each section to confirm you are getting results before joining them.

    So try:

    =COUNTIFS({MB Intake Audits - 2024 Type of Audit}, "DD"

    =COUNTIFS({MB Intake Audits - 2024 Auditor Name}, "Brian"

  • Joey135
    Joey135 ✭✭✭✭
    Options

    =COUNTIFS({MB Intake Audits - 2024 Type of Audit}, "DD" formula works but the Brian one does not. Currently my column is text but it will change to a contact list.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!