COUNTIF with Range

LoganDavison
LoganDavison ✭✭✭
in Formulas and Functions

Hello! I'm using a COUNTIF formula to reference the number of times columns from a form are answered yes and no in the last two weeks. =COUNTIF({Range 3}, "No")

The problem is when new forms are completed, the responses appear at the top of my sheet. I only want the range to be entries from the last two weeks (28 total), but the range does automatically hold to rows 1-14. It jumps down when new rows are added and holds to the original selected cells from the range selection. Any idea on how to adjust this?

· Share on FacebookShare on Twitter

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 11/19/20

    Hi @LoganDavison

    hope you are fine, i hope the following will answer your question.

    to make any function like COUNTIF not restricted to range of cells when you select the range don't select by cell for example ( cell1:cell14) but let the selection open to include any new entry by deleting the number of the cell from selection (cell:cell) the formula will be:

    =COUNTIF(List:List,criterion)

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on FacebookShare on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @LoganDavison

    You'd need to use the COUNTIFS function instead and add criterias, so it only includes the data for the last two weeks.

    Make sense?


    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    Would that work/help?


    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

    · Share on FacebookShare on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @LoganDavison

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!