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

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

                        spreadsheet using SUMPRODUCT and COUNT

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.