2

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

Improve this question
4
  • 1
    You can copy this formula to another cell and change SUM to COUNT.
    – Fábio Perez
    Commented Dec 20, 2012 at 11:46
  • @FábioPerez Quick add it as an answer.
    – John K. N.
    Commented Dec 20, 2012 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
    Commented Dec 20, 2012 at 12:21
  • @toby I'm not sure but I think you need to create a macro for this.
    – Fábio Perez
    Commented Dec 20, 2012 at 13:29

2 Answers 2

Reset to default
1

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

Improve this answer
0

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
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .