COUNTIF for cells not in sequential order

Alison Antisz

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?

Mike Wilday

Thanks that helps a ton. I would put the following formula in your first cell, delete the rest of the column, then right-click on this formula to make it a column formula:

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

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

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.

Mike Wilday

Thanks that helps a ton. I would put the following formula in your first cell, delete the rest of the column, then right-click on this formula to make it a column formula:

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

Sameer Karkhanis

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.

SK

Alison Antisz

Thanks Mike, this appears to work.

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