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.

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


    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


    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.

Participate now!

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