Countifs

Hello

I am having trouble with this formula.

I am trying to count the total number of entries per week. Here's the formula

=COUNTIFS({NESV Tracker Range 1}, "5GC" + "EPC", {NESV Tracker Range 2}, >=[Column2]@row, {NESV Tracker Range 2}, <=[Column3]@row)

I get 0's when there should be different results.


If I do "5GC" alone it works:

Or if I do "EPC" it also works:

I need a formula that added both


Thanks

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Cristian

    The zero is caused by the way you are describing the EG and 5GC. As you have it written, smartsheet thinks you are concatenating the two to become 5GCEG. The Formula correctly returns a zero for that term.

    In your data set, are the choices a single select cell, or multiselect (it could have both EG and 5GC in the same cell)? The formula below assumes a single-select cell based on your working formulas above.

    =COUNTIFS({NESV Tracker Range 1}, OR(@cell="5GC", @cell="EPC"), {NESV Tracker Range 2}, >=[Column2]@row, {NESV Tracker Range 2}, <=[Column3]@row)

    Will this work for you?

    Kelly

  • Cristian
    Cristian ✭✭✭✭

    Hi @Kelly Moore

    The formula works. Thanks.

    And thanks for making me understand the concatenating portion of formula. Also, thanks for clarifying the @cell portion. Did not know that. Thanks again.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!