Countifs with a lookup

[DRExcel515]

So I have a bunch of column identifiers in column A on Sheet 1 (AVG_EFC_MTY_YR_QT EG). On sheet 2 I have the same column identifiers, except that they go across starting in A1 through IN1 and have data below them in the columns. What I wanted to do on sheet 1 in column B is say go and count all the actual populated data in this column on sheet 2 and exclude if the value is equal to (null). Here is the formula I was trying to build but doesn't seem to work. Any help would be much appreciated!

Thanks,
COUNTIFS(INDEX(Sheet2!1:1,MATCH(Sheet1!$A2,Sheet2!1:1,0)),Sheet1!$A2,Table1,"<>(null)")

 

[oldbrewer]

cola	colb	colc							cola	colb	colc
33	174	707							3	9	205
									4
										66
									5
										55
									6
									7
									8	44	502
		ignore separate sheets for now - what do you want in A2:A4 ?

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[oldbrewer]

cola	colb	colc							cola	colb	colc
6	4	2							3	9	205
									4
										66
									5
										55
									6
									7
									8	44	502
			formula in A2 is
			=SUMPRODUCT((J2:J100<>"")*1)

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>