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

Jeanne

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

Paul Newcome

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

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

Krissia B.

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

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

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

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!

Jeanne

Paul Newcome

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