COUNTIF with Range

11/19/20
Answered - Pending Review

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?

Answers

  • Bassam.M KhalilBassam.M 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)

    Best Regards

    Bassam.M Khalil


    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!

  • 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, [email protected])

    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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.