Toggle cell value inclusion in count formula

[MacroShark]

In Excel 2010 I have a range of cells that each contains a formula. This range of cells is counted using '=COUNT("Range")'.

I would like to use VBA to exclude a cell from being counted, but also being able to change my mind and include it again. So I want to be able to toggle if a cell is included or not. Using standard Excel (delete and undo) wont help me since the toggle could happen over time.

Any suggestions on how to achieve this? I would greatly appreciate it.

The shark

 

[Yongle]

you could do this without VBA

with data validation in cell A1, source list:= true,false
then user this formula in a cell, where the 2nd COUNT excludes the cell (below A5 is not included in the count)
=IF(A1=TRUE,COUNT(A2:A9),COUNT(A2:A4,A6:A9))
Toggling that cell between true and false (via the dropdown) does what you want

 

[MacroShark]

Thank you Yongle for your suggestion.

I am looking for a more flexible solution though. It could be that there is one or more cells in the range I want exclude/include. So really what I am looking for is some way to get the count formula to ignore a cell value.

 

[Yongle]

Try something like this.
The input box allows user to select one or more cells for exclusion
Using shift key allows for contiguous cells
Holding down Ctrl key alows individual cells to be selected
Amend the range to reflect your required range

Sub CountAsRequired()
    Dim rng1 As Range, rng2 As Range, c1 As Long, c2 As Long, msg As String
    msg = "Select cells to be excluded from count" & vbCr & "Use shift\ ctrl keys to select multiple cells"
    Set rng1 = Range("[COLOR=#ff0000]A1:J8[/COLOR]")
    c2 = 0
    c1 = WorksheetFunction.Count(rng1)
    If MsgBox("Count all cells?", vbYesNo, "Count") = vbNo Then
        Set rng2 = Application.InputBox(msg, "EXCLUDE", , , , , , 8)
        c2 = WorksheetFunction.Count(rng2)
    End If
    MsgBox "Count = " & c1 - c2
End Sub

 

[MacroShark]

Hi Yongle, thank you. Your code works fine.

 

[nikio8]

you could also use sumifs,
say you have another column with Y and N
Sumifs(A:A, B:B,"Y")

 

[MacroShark]

Sweet and simple - thanks!

 

[nikio8]

Hehe
You could also use data validation on the column and only allow say y and n.
That way someone cannot enter not, yes.