Count of all non-blank rows in all remaining columns in a specified range, based on the filter in first column

[TanviP]

I have 4 columns like:

	Team 1 <tbody> </tbody>	Team 2 <tbody> </tbody>	Team 3 <tbody> </tbody>
Name <tbody> </tbody>	John <tbody> </tbody>	Shaun <tbody> </tbody>	Haley <tbody> </tbody>
	Mike <tbody> </tbody>	Divya <tbody> </tbody>	Brittany
	Alex <tbody> </tbody>		Molly <tbody> </tbody>
School <tbody> </tbody>	ABC <tbody> </tbody>	DEF <tbody> </tbody>
	DEF <tbody> </tbody>	GHI <tbody> </tbody>
		ABC <tbody> </tbody>

<tbody>
</tbody>

I need to find count of non blank rows in Team 1, Team 2, and Team 3, but filtered by the Name and School.
The result should look like:

	Team 1	Team 2	Team 3
Name	John	Shaun	Haley
	Mike	Divya	Brittany
	Alex		Molly
	3	2	3
School	ABC	DEF
	DEF	GHI
		ABC
	2	3	0

<tbody>
</tbody>

 

[Jobolobo]

Hey TanviP,

Maybe give this a go. Using the "CountA" formula at the bottom of each of your columns.

CountA will count the non blank cells.

=CountA(Range)

	A	B	C	D
1		Team 1	Team 2	Team 3
2		John	Shaun	Haley
3	Name	Mike	Dyvia	Brittany
4		Alex		Molly
5		=CountA(B2:B4)	=CountA(C2:C4)	=CountA(D2:D4)

<tbody>
</tbody>

The results should show a count of the non blank cells in each column.

 

[TanviP]

I need to write a macro for this to be done automatically on the click of a button. And I'm new to Excel and VBA

 

[TanviP]

I need to write a macro for this to be done automatically on the click of a button. And I'm new to Excel and <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>