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

Thanks that helps a ton. I would put the following formula in your first cell, delete the rest of the column, then rightclick 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")
Answers

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.

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.

Thanks that helps a ton. I would put the following formula in your first cell, delete the rest of the column, then rightclick 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")

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

Thanks Mike, this appears to work.
This is a very inconvenient solution and I hope that SS improves this interface.
Help Article Resources
Categories
Check out the Formula Handbook template!