I want a formula that counts (Request Type by Month).

I have a metrics sheet and the RE Execution Request sheet and have attempted many versions of this formula for the highlighted cell:

=COUNTIFS({HEOR Research Execution Request Form Range 4}, AND(IFERROR(MONTH,0) = 8, IFERROR(YEAR,0) = 2021, {HEOR Research Execution Request Form Range 1}@cell = "Prospective Research Agreement",{HEOR Research Execution Request Form Range 2}@cell = "New")


Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    It looks like we might have a few syntax issues. Try this...

    =COUNTIFS({HEOR Research Execution Request Form Range 4}, AND(IFERROR(MONTH(@cell),0) = 8, IFERROR(YEAR(@cell),0) = 2021), {HEOR Research Execution Request Form Range 1}, @cell = "Prospective Research Agreement",{HEOR Research Execution Request Form Range 2}, @cell = "New")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    There were primarily 3 issues.

    1: The MONTH and YEAR functions need to reference something. Thus the MONTH(@cell) and YEAR(@cell) adjustments.

    2: You forgot to close your AND statement around that particular criteria set. Think of it this way... Each range has ONE criteria. The AND function is that single criteria. It just happens to contain multiple criteria sets within it.

    3: There were missing commas between the range and criteria in the second and third range/criteria sets (the same way you had a comma between the first range and its criteria).


    =COUNTIFS({HEOR Research Execution Request Form Range 4}, AND(IFERROR(MONTH,0) = 8, IFERROR(YEAR,0) = 2021, {HEOR Research Execution Request Form Range 1}@cell = "Prospective Research Agreement",{HEOR Research Execution Request Form Range 2}@cell = "New")

    =COUNTIFS({HEOR Research Execution Request Form Range 4}, AND(IFERROR(MONTH(@cell),0) = 8, IFERROR(YEAR(@cell),0) = 2021), {HEOR Research Execution Request Form Range 1}, @cell = "Prospective Research Agreement",{HEOR Research Execution Request Form Range 2}, @cell = "New")

Answers

  • Krissia B.
    Krissia B. Moderator

    Hello @Jeanne

    Thank you for your screenshots of the sheet setup! Upon reviewing and testing on this, I'm unable to confirm what the formula is trying to do. May you please explain what you're trying to count and what criteria needed to meet for the COUNTIF function to happen.


    Cheers!

    Krissia

  • Jeanne
    Jeanne ✭✭✭
    edited 08/31/21

    Hi @Krissia B

    Thank you for trying to help! I am new to Smartsheet and these formulas. Basically I am trying to do the following:



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    It looks like we might have a few syntax issues. Try this...

    =COUNTIFS({HEOR Research Execution Request Form Range 4}, AND(IFERROR(MONTH(@cell),0) = 8, IFERROR(YEAR(@cell),0) = 2021), {HEOR Research Execution Request Form Range 1}, @cell = "Prospective Research Agreement",{HEOR Research Execution Request Form Range 2}, @cell = "New")

  • Jeanne
    Jeanne ✭✭✭

    Thank you so much! It works!! I will compare the two (old vs. yours) and better understand what I was doing wrong, but I really appreciated the help!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    There were primarily 3 issues.

    1: The MONTH and YEAR functions need to reference something. Thus the MONTH(@cell) and YEAR(@cell) adjustments.

    2: You forgot to close your AND statement around that particular criteria set. Think of it this way... Each range has ONE criteria. The AND function is that single criteria. It just happens to contain multiple criteria sets within it.

    3: There were missing commas between the range and criteria in the second and third range/criteria sets (the same way you had a comma between the first range and its criteria).


    =COUNTIFS({HEOR Research Execution Request Form Range 4}, AND(IFERROR(MONTH,0) = 8, IFERROR(YEAR,0) = 2021, {HEOR Research Execution Request Form Range 1}@cell = "Prospective Research Agreement",{HEOR Research Execution Request Form Range 2}@cell = "New")

    =COUNTIFS({HEOR Research Execution Request Form Range 4}, AND(IFERROR(MONTH(@cell),0) = 8, IFERROR(YEAR(@cell),0) = 2021), {HEOR Research Execution Request Form Range 1}, @cell = "Prospective Research Agreement",{HEOR Research Execution Request Form Range 2}, @cell = "New")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!