# COUNTIF for value in Data Validated Cells

• Hi all,

How would I be able to restrict a countif function to data validated cells only?

Lets say I'm trying to get the count for the word "Yes" in all data validated cells.

Only thing I've got so far

Code
``ActiveCell.SpecialCells(xlCellTypeAllValidation).Select``

Thanks!

• 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.

Bruce :cool:

• 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.

Code
``````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``````

Bruce :cool:

• Re: COUNTIF for value in Data Validated Cells

Yes, there will be non-validated cells containing the word "yes'. Would you be able to return the count value in cell C1, (please) ?

Thanks Skywriter!

• Re: COUNTIF for value in Data Validated Cells

Just replace the whole msgbox line with:

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

Bruce :cool:

• Re: COUNTIF for value in Data Validated Cells

Works perfectly!

Thanks a ton Skywriter, can I make a donation to a charity of your choice? Maybe Auscam freedom project for the DHF?

GC052

• Re: COUNTIF for value in Data Validated Cells

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

Bruce :cool:

• Re: COUNTIF for value in Data Validated Cells

Ah that's awkward, tried to donate, but I'm getting an error since I'm not located in Aussie. Any other charities that can take PayPal or a Canadian CC?

• 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.

Bruce :cool:

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!