Generate a summary table in Excel for multiple variables without PivotTable

I am trying to build a template in excel that summarizes a dataset according to test, sample type, and analyzer. Sorry - I've never posted a question about excel before, so please tell me if more info needed.

I need to calculate the Mean, Standard Deviation, and the Measurement Uncertainty for each test, separated by sample type and analyzer (MU = 1.96 x SD). My actual dataset has a few hundred 'Tests' two or three 'Types' for each, and two or four 'Analyzers'. There should be dozens of data points for each 'Result'. I will also make a few additional calcs, but keeping it simple for clarity.

Here is example data:

Test	Type	Name	Result
pH	Water	Analyzer1	6.1
pH	Water	Analyzer2	6.5
pO2	Water	Analyzer1	55
pO2	Water	Analyzer2	64
pH	Water	Analyzer1	6.3
pH	Water	Analyzer2	6.8
pO2	Water	Analyzer1	50
pO2	Water	Analyzer2	68
pH	Buffer	Analyzer1	5.9
pH	Buffer	Analyzer2	6.4
pO2	Buffer	Analyzer1	48
pO2	Buffer	Analyzer2	71
pH	Buffer	Analyzer1	6.4
pH	Buffer	Analyzer2	6.9
pO2	Buffer	Analyzer1	51
pO2	Buffer	Analyzer2	64

The result I am after looks like this:

Test	Type	Name	Mean	SD	MU
pH	Water	Analyzer1	6.2	0.35	0.69
pH	Water	Analyzer2	6.7	0.35	0.69
pH	Buffer	Analyzer1	6.2	0.14	0.28
pH	Buffer	Analyzer2	6.7	0.21	0.42
pO2	Water	Analyzer1	52.5	3.54	6.93
pO2	Water	Analyzer2	66.0	2.83	5.54
pO2	Buffer	Analyzer1	49.5	2.12	4.16
pO2	Buffer	Analyzer2	67.5	4.95	9.70

I am trying to automate this as much as possible, but don't want to use VBA. Unless I am missing something, I can't use a PivotTable as I have to use the Mean/SD to calculate the MU. Thus far, I have tried creating a new table using:

=UNIQUE(FILTER(B:B,A:A="pH"))

which generates an array containing Buffer and Water, but I can't include AND() in the FILTER() function to further parse the data. I have tried a bit of XLOOKUP() but that doesn't seem to work either.

Very interested to hear an approach!

***Edits: I am using Microsoft® Excel® for Microsoft 365 MSO (Version 2408 Build 16.0.17928.20336) 32-bit Previous responses have employed GROUPBY() or PIVOTBY() but these functions do not appear present in this version of Excel.

Solution

Here is one way of accomplishing the desired output, using MAKEARRAY():

=LET(
     _Data, A1:D17,
     _Header, HSTACK(TAKE(_Data,1,3),"Mean","SD","MU"),
     _Body, DROP(_Data,1),
     _Catg, TAKE(_Body,,3),
     _Uniq, UNIQUE(_Catg),
     VSTACK(_Header, SORT(HSTACK(_Uniq, ROUND(MAKEARRAY(ROWS(_Uniq), 3, LAMBDA(r,c, 
     INDEX(LET(a, CHOOSECOLS(_Body,-1), 
     b, BYROW(_Catg, LAMBDA(x, AND(x=CHOOSEROWS(_Uniq, r)))), 
     d, FILTER(a,b), HSTACK(AVERAGE(d), STDEV(d), 1.96*STDEV(d))),c))),2)),{1,2})))

Or, Using GROUPBY()

=DROP(GROUPBY(A1:C17,D1:D17,HSTACK(AVERAGE,STDEV,LAMBDA(x,1.96*STDEV(x))),1,0),1)