COUNTIF for cells not in sequential order


Hi Community

I am trying to create a countif formula for cells that are not in sequential order. To select the range I am holding down the Ctrl key and the cells appear to be live. When I finish my selection I place a , "criteria") but the formula comes back as incorrect. When I have cells that are sequential I do not experience this problem.

Can someone advise?

Best Answer


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Can you show an example of what you're trying to count? Seeing a sample of the data would be very helpful in supporting you.

  • Alison Antisz
    Alison Antisz ✭✭✭✭

    Hi Mike,

    I am trying to count a simple "Yes" answer from a drop down menu but the formula cell is about 25 rows left of the range that I want to select. The columns where the "Yes" selections make up the range are then every other column for 17 cells. Since the sheet is over 80 columns I can't easily clip the image.

    What I am experiencing is if I use =COUNTIF(range, "Yes") where the range cells are in sequential order (left to right) there is no problem. When I want to individually select cells that are not next to each other I hold down the ctrl button and click the cells, they are given a color and appear live in the formula but once I close the formula it gives the error.

    The below example may be helpful. This the part of the sheet. In column 23) COVID I want to count the number of cells with Completed in row1 of columns 1a., 3a and 5a.

    This shows the live formula example

    And #INCORRECT shows up in the cell.

    Let me know if this is unclear.

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    edited 12/05/20

    When you use a range, COUNTIF calculates the sum automatically that matches the criteria, however when you have selective cells there is only one cell so you need to add the sum logic for multiple countif, so your formula will need to be along the lines of,

    =SUM(COUNTIF([1a. Stirring Stick]@row, "Completed"), COUNTIF([3a. Plastic Water Bottle]@row, "Completed"), COUNTIF([5a. Plastic Wrappers]@row, "Completed"))

    Hope this helps.


  • Alison Antisz
    Alison Antisz ✭✭✭✭

    Thanks Mike, this appears to work.

    This is a very inconvenient solution and I hope that SS improves this interface.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!