Count and Sum if column name is exact match

I am working with a large dataset (500+ columns X 10,000 rows). I am trying to get a count and sum of all variables within a row. All column names are listed on a separate worksheet/table. Whats the best way to go about it? Thanks

Main Data: Raw Data
Summary: Where I am trying to get the count and sum

Formula:

One way with ms365 could be:

Formula in G3:

=LET(z,B2:E7,WRAPROWS(TOCOL(VSTACK(TAKE(z,1),BYCOL(DROP(z,1),LAMBDA(x,COUNT(x))),BYCOL(DROP(z,1),LAMBDA(y,SUM(y)))),,1),3))

It can be less verbose, but this way you'd just need to select B2:E7 once, on whatever worksheet it is located.

PowerQuery:

Another way would be PowerQuery, where you'd:

• Select (a single cell or ) the whole range B2:E7;
• On the 'Data' tab, click 'From Table/Range' in the 'Get & Transform Data' group;
• Choose to include headers, and hit 'OK'. PQ launches;
• Select all column and in the 'Transform' tab, click 'Unpivot Columns' under the 'Any Column' group;
• Select the 1st column, 'Attribute' in this case, and on the same tab 'Transform' choose to 'Group By' under the 'Table' group;
• Hit the advanced option and create two aggregations before hitting 'OK':

• Close and load data back to Excel.

If you Summary (Trying) column name is not fixed ... something like this :

So, the Sheet1 is data with 4 columns header, but in Sheet2, the "demand" is only 3 columns and not in sequence. (and later on, maybe the "demand" change to only 2 columns, later on the "demand" to all 4 columns, etc and also the header name always change).

The expected result in Sheet2:<br>

So, if the cell has value, even if it's a zero value in the data column A then it is counted. Only a blank cell is not counted.

Sub test()
Dim rgSrc As Range: Dim rgTrg As Range: Dim c As Range

Set rgSrc = Sheets("Sheet1").UsedRange 'change as needed

With Sheets("Sheet2")
Set rgTrg = .Range("A2", .Range("A2").End(xlDown)) 'change as needed
End With

For Each cell In rgTrg
    Set c = rgSrc.Rows(1).Find(cell.Value, lookat:=xlWhole)
        If Not c Is Nothing Then
            With rgSrc.Columns(c.Column)
                cell.Offset(0, 1).Value = Application.CountA(.Cells) - 1
                cell.Offset(0, 2).Value = Application.Sum(.Cells)
            End With
        End If
Next

End Sub

rgSrc is the range of the data in sheet1.<br>
rgTrg is the range from cell A2 to whatever lastrow in sheet2. The code assumed that there'll be no blank cell in between data in column A of sheet2.

It loop to each cell in rgTrg (loop to each demanded header name), then put the count of the data in rgSrc with the looped cell value (which is the header name) in cell.offset(0,1) and put the sum of it in cell.offset(0,2).