PDA

View Full Version : Count values in rows based on column name



Sanjay Joshi
07-25-2018, 01:53 PM
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
07-25-2018, 03:38 PM
It is better to upload sample of your workbook with the desired output