sum count of rows in changing length lists

Hi

Could someone tell me if it is possible to do a count of the number of rows in different groups of lists in Excel rather than VBA? Doing it in VBA is not a problem but wanted to know if an Excel function can handle it.

For e.g

Numbered Group1 list starts in cell A3 and is 9 rows long.
Numbered Group2 list starts in cell D3 and is 2 rows long.
Numbered Group3 list starts in cell A20 and is 5 rows long.
Numbered Group4 list starts in cell D20 and is 14 rows long.

These groups can all change length at anytime so a simple =sum(value1,value2,value3,value4) won't do.

Thank you for any help / pointers.

Hi

So why asking in a VBA forum ?‼

Hi

Apologies. I have tried Excel and suspect I will need VBA. Sorry didn't phrase my question properly, meant to say if not possible in Excel how to do in VBA with a changing length list.

OK - test sheet attached. Like I mentioned list length in each group can change so that's the issue I am having with Excel so think I need VBA.

Simple enough issue I think ... just not for me !

If you convert each list range to a true Excel table then
it would be so easier to count the number of all lists names via a VBA procedure …

Or if the 4 yellow cells are fixed and that's never change then it's easy without converting ranges to Excel tables …

If there is no other data in that sheet.
If there is, the "UsedRange" needs to be changed to the actual Range with the blocks of data to be counted.

Please Login or Register  to view this content.

Maybe
=COUNTIF(B:B,"?*")+COUNTIF(E:E,"?*")-4

Another possibility.
Sub Maybe_B()
MsgBox WorksheetFunction.CountA(Sheets("Sheet1").Range("B:B, E:E")) - WorksheetFunction.CountIf(Sheets("Sheet1").Range("A1:E25"), "Group" & "*")
End Sub

Thanks AliGW.

That sort of works but if there is some other text in the column, it seems to include that in the count also (when I don't need that including). Example attached. Sorry should have made that clear earlier. Example attached.

How about
=COUNT(A1:E50)

Haha!

But what about the fact that
1. The lists can change length
2. Other text can appear in and around the cells which are not related to the number of names

Once again apologies .. I’ve realised I could have been clearer in my original post. Essentially I need a count of the number of names only but the number of names in each group keeps changing on a weekly basis.

Thanks again

Change the formula range to make it large enough to cover all eventualities.

Thanks jolivanes wiil try it out.

That formula only counts the cells that contain numbers & therefore any additional text is irrelevant.

Thankyou

You're welcome & thanks for the feedback