COUNTIF for value in Data Validated Cells

Re: COUNTIF for value in Data Validated Cells

Can you upload an example workbook?

When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions.

Re: COUNTIF for value in Data Validated Cells

I'm assuming you want to use only the validation cells because there are other non validation cells with the word "Yes" in them?

The reason I ask is if I create a range using the validation cells and attempt to use countif on that range there is an error.

I believe the error is because the range has non contiguous cells.

I can however loop through these cells and get an answer by just keeping count of the cells with "Yes" in them.

If there are no other cells with "Yes" in them besides the validation cells you can just use countif on all the used range cells in the worksheet.

Sub gc052()
Dim r As Range, y, myCount As Long
myCount = 0
Set r = Worksheets("Template 1").UsedRange.SpecialCells(-4174)
For Each y In r.Cells
    If y.Value = "Yes" Then myCount = myCount + 1
Next y
MsgBox myCount
End Sub

Re: COUNTIF for value in Data Validated Cells

Just replace the whole msgbox line with:

Worksheets("Template 1").Range("C1").Value = myCount

Re: COUNTIF for value in Data Validated Cells

Go to this thread and there's a user named Pike, post #2.
Link.
Click on his linked charity and donate to them.
I'm sure we'll both appreciate it.

Re: COUNTIF for value in Data Validated Cells

I sent pike a message to check on it.

Thanks for the consideration.

I checked google for Australia time and it's around 2:00AM there so it might be a few hours, maybe more I don't know what how often pike checks the board.