excel countif entire row in a table

I have a structured table in excel 2016.

I want to have a cell to count the number of cells across the entire row within the table if it matches my criteria.

I have tried putting this formula in column A on each row =COUNTIF(Table[@],"my criteria") but that does not count properly.

However, this works: =COUNTIF(Table[@[ColB]:[ColH]],"my criteria"). But since my table will expand, I don't want to specify the column name [ColB], I want to refer to the entre row in the table.

formula = =COUNTIF(Table[@],"A") does not work

formula = =COUNTIF(Table[@[colC]:[colE]],"A") works

My table will expand both horizontally and vertically.

Please Note: This solution is only available if you chose to use VBA. VBA does not work in web versions of Excel. Please ensure that you resave your workbook as a macro-enabled workbook before proceeding.

You can choose to use VBA and create your own custom worksheet formula. Since this question didn't start out as a VBA issue, I will be a bit more detailed on the process of setting this up as opposed to just throwing you some code and you having to figure out what to do with it.

1. After you've re-saved your workbook as a macro-enabled workbook, open the VBA Editor (VBE) by pressing Alt and F11 simultaneously.
2. In the VBE, click Insert > Module. You should now see Module1 highlighted on the left side bar (The project Explorer).
3. Copy and paste the following code in the blank area of the module:

Option Explicit

Public Function GetMyRowCount(Criteria As Variant) As Long

    Dim ws As Worksheet
    Dim tblRng As Range, RowRng As Range
    With Application.Caller
        Set ws = .Worksheet
        Set tblRng = ws.Range(.ListObject.Name)
        Set RowRng = ws.Range(ws.Cells(.Row, .Column + 1), ws.Cells(.Row, tblRng.Columns.Count))
    End With
    
    GetMyRowCount = Application.WorksheetFunction.CountIf(RowRng, Criteria)

End Function

4. Now use this UDF (User Designed Function) in your worksheet. In the column you would like the calculation to be in, simply type =GetMyRowCount("My Criteria") and it should calculate.

To point out how this code works in more detail:

• Application.Caller is referring to the cell that this function is located in. Because we now know the location of the cell, VBA can use it's location to obtain the row data from it (which is why you don't need an argument for the row #).

• RowRng is getting the starting point of the column within the ws.Range(...) function with the first ws.Cells(...) function. .Row is the row # from the GetMyRowCount function (using Application.Caller.Row method), and the 3 is simply the static column C.

• The way we grab the last column we need is by counting the total # of columns within the table: ws.Cells(.Row, tblRng.Columns.Count)

Using the information we obtained from bullets 2 and 3, we can establish the entire range of the lookup we need, and then place this range into your CountIf() function, along with the criteria you passed with the function's argument:

GetMyRowCount = Application.WorksheetFunction.CountIf(RowRng, Criteria)

As you can see in the following example, I wanted to count the number of times in the row the number 1 occurred:

Another example showing it works with text as well by using "Apple" as the criteria: