Selected Answer
Unfortunately, Excel doesn't have a function like the one you describe. But it isn't difficult to create one. In Excel terminology it is called a "User Defined Function", in short UDF. It works just like any of Excel's built-in functions but you are in full control of the code. Here is the code.
Function Summary(Crit As String, _
CritRow As Long, _
CapsRng As Range) As String
' 29 Oct 2018
Dim Fun() As String
Dim Target As Range
Dim Crits As Variant
Dim Caps As Variant
Dim C As Long
Dim n As Integer
Application.Volatile
With CapsRng
Set Target = Range(Cells(CritRow, .Column), _
Cells(CritRow, .Column + .Columns.Count - 1))
Caps = .Value
End With
Crits = Target.Value
ReDim Fun(UBound(Caps, 2))
For C = LBound(Crits, 2) To UBound(Crits, 2)
If StrComp(Crits(1, C), Crit, vbTextCompare) = 0 Then
Fun(n) = Caps(1, C)
n = n + 1
End If
Next C
If n Then
ReDim Preserve Fun(n - 1)
Summary = Join(Fun, ", ")
Else
Summary = "N/A"
End If
End Function
This code must be installed in a standard code module. Note that every workbook comes with a number of special (class) modules but no standard module. You will have to create it. It's easy. Press Alt+F11 to open the VB Editor. Find your workbook in the Project Explorer window on the left of the Editor's screen and right-click on it. In the drop-down that opens select Module. Excel will insert the new, blank module on the right of your screen. It will replace another module - one of the built-in ones - which is also blank. To see the difference look at the module's name in the window's title. You will see [Module1 (Code)] appended to the workbook's name. Paste the above code in that code module. Save the workbook in XLSM format.
That's all it takes. The UDF is now available anywhere in the workbook. Its name is Summary, and this is how you call it, for example:-
=Summary("F", ROW(),$B$1:$I$1)
- "F" stands for the criterium you want to summarise. You can use "F" or "Y". The criteria aren't case sensitive.
- ROW() is a requirement. It just tells the function which row to look at.
- $B$1:$I$1 is the address of the column captions you want to use in the summary. The range can have any number of cells but they must be contiguous.
You can have two formulas next to each other (in different columns), one for "Y" and the other for "F". You can concatenate the formula with other text, for example,
="Fluctuating: " & Summary("F", ROW(),$B$1:$I$1)
Copy the formula down as you would with any built-in function.
