[Solved] Count next cell value of given cell value

[Venkatesh]

Assume A1 to A10 have value of 11 12 13 14 15 16 17 18 19 & 20
and B1 to B10 have value of 9 8 7 6 5 4 3 2 1 0

Need formula or VBA - while giving reference cell value, it should be to count respective downwards cell value.

If i give A4 as 14 And A9 as 19, formula to be count B4 to B9 (6,5,4,3,2,1) = 21

 

[Yousuf Shovon]

Assume A1 to A10 have value of 11 12 13 14 15 16 17 18 19 & 20
and B1 to B10 have value of 9 8 7 6 5 4 3 2 1 0

Need formula or VBA - while giving reference cell value, it should be to count respective downwards cell value.

If i give A4 as 14 And A9 as 19, formula to be count B4 to B9 (6,5,4,3,2,1) = 21

Hello Venkatesh,

Welcome to our ExcelDemy forum! I understand you wish to sum respective downward cell values while providing reference cell values. You can do this using both formula and VBA methods. However, the VBA methods seem easier in this case. Try the following VBA code:

Sub CountCells()
    Dim refCell As Range
    Dim count As Long
    Dim sum As Long
    Set refCell = Application.InputBox("Please select a reference cell", Type:=8)
    count = Application.WorksheetFunction.CountA(refCell.Offset(1, 0).Resize(10 - refCell.Row))
    sum = Application.WorksheetFunction.sum(Range("B" & refCell.Row & ":B10"))
    MsgBox "Count of cells below " & refCell.Address & ": " & count & "|" & refCell.Value & vbNewLine & _
           "Sum of values in B" & refCell.Row + 1 & " to B10: " & sum
End Sub

If you wish to solve this with a formula, you can try merging SUM, INDEX & MATCH functions. See our https://www.exceldemy.com/sum-index-match-multiple-criteria-in-excel/ article for a better understanding. I hope this works for you. Good luck!

Regards,
Yousuf Shovon