# I want to count the number of cells included in a formula

I've been looking for this answer and I'm sure it's embarrassingly simple but I can't find it anywhere.

I have a very simple but large formula =SUM(H2:H5,H7,H22:H23,H28:H30,H33,H41,H43:H46.....etc) and in the next door cell I want it to show the count of how many cells exist in this sum without having to count every individual instance of each cell manually. Thanks

• You can copy this formula to another cell and change `SUM` to `COUNT`. – Fábio Perez Dec 20 '12 at 11:46
• @FábioPerez Quick add it as an answer. – hot2use Dec 20 '12 at 12:19
• Ha great thank you! Knew it was something simple.Just for complications sake say the addition of cells was in constant change in the =SUM(H2:H5,H7,H22:H23,H28:H30...etc) part and I didn't want to keep having to copy and paste the new (H2:H5,H7,H22:H23,H28:H30...etc) in to the =COUNT(H2:H5,H7,H22:H23,H28:H30...etc) cell. Is there any way of having this done automatically? eg if the =SUM(x) was in J1 is there a way of gathering all the info already in that cell so you could just do =COUNTCONTENT(J1) ? – toby Dec 20 '12 at 12:21
• @toby I'm not sure but I think you need to create a macro for this. – Fábio Perez Dec 20 '12 at 13:29

Create a new column that flags the rows in column `H` that you want to include in the SUM:

This may make it easier to maintain the row selection.  For example, if it is based on other data (e.g., SUM the budgets of projects that are behind schedule), column `I` could be a formula.  Also, this would let you conditionally format column `H` to indicate which cells are included in the sum.  (If column `I` is populated by a formula that evaluates to `0` or `1`, as opposed to (blank) or `1`, then the formula in `K1` should be changed from `=COUNT(I:I)` to `=SUM(I:I)`.)

The following user defined function, COUNTSUMCELLS(), will return a count of the cells used in in a sum formula. Usage is straightforward - if cell A1 contains the sum formula, then COUNTSUMCELLS(A1) will return the count of the cells used in the formula.

``````Function CountSumCells(sumCell As Range) As Long

' Returns a count of the cells used in a sum formula. If there is a
' circular reference, returns #VALUE!.

Dim sFormula As String

sFormula = sumCell.formula
sFormula = Mid(sFormula, 6, Len(sFormula) - 6)

If Not (Intersect(Application.Caller, Range(sFormula)) Is Nothing) Then
CountSumCells = CVErr(xlErrValue)
End If

CountSumCells = Range(sFormula).count

End Function
``````