Count values in rows based on column name

[Sanjay Joshi]

Hi,

I have an excel file with column names in first row. Let say there are around 30 columns to count including text value and numeric values in different rows. Now to count the non blank cells/rows within these columns, I can use CountA function however the catch here is, some columns' name is similar like ColumnA_1,ColumnA_2,ColumnA_3, ColumnB_1,CoulmnB_2,ColumnB_3 etc.

So the requirement is, if there is value in any of the cell within these similar columns, it should be counted as 1.For example if in first row, ColumnA_1 and ColumnA_2 and ColumnA_3 have values, it should be counted as 1 only. Similarly if any row has no data for ColumnB_1 but has data for ColumnB_2 and ColumnB_3, it should also be counted as 1. Rest of the variables should be counted accordingly.

The output I'm looking is something like below (output at column J).

A	B	C	D	E	F	G	H	I	J	K
ColumnA_1	CoulmnA_2	CoulmnA_3	CoulmnB_1	CoulmnB_2	CoulmnB_3	CoulmnC	CoulmnD		Expected Counts	Notes
1	1		1	1	1	1,2,3	text		4	count columns A,D,G,H
	5	3			6	6,2	name		4	count columns B,F,G,H
2	8	1	17	9		4,9,11,7			3	count columns A,D,G

Above table is attached as well.

Can you provide any macro for above calculations please.

Many Thanks,
Sanjay

[YasserKhalil]

It is better to upload sample of your workbook with the desired output