Standard Deviation on Spilled Range with IF

[Winston]

I currently have two spilled ranges of data starting in row 3 of column AH and AH as shown:

I wanted to determine the Average and Standard Deviation in scores for each reviewer in the spilled ranges. I created a list of Unique Reviewers (column AK) using the UNIQUE function that returned a spilled range in row 3, as expected. I then was able to use the AVERAGEIFS function to create the average score (column AL) for each unique reviewer, returning a spilled range using:

=AVERAGEIFS($AH$3#,$AG$3#,AK3#)

My challenge now is doing the same to calculate the Standard Deviation for each unique reviewer (Column AM). I was able to simply use the following and manually drag down into each row of column AM for this:

=STDEV(FILTER($AH$3#,AK3=$AG$3#))

but, this is not a spilled range and requires me to manually adjust the column length if there are any changes in the spilled range size of the other columns.

My Question: Is there a single formula I can use in cell AM3 that will result in a spilled range result of standard deviation for each unique reviewer, similarly to the AVERAGEIFS function?

 

[StephenCrump]

Welcome to the Forum!

At the risk of embarrassment, I'll stick my neck out and say you can't do it in a single array formula using STDEV.

But with a bit of contrivance: =SQRT(TRANSPOSE(MMULT(SEQUENCE(,ROWS(AG3#),1,0),--IF(AG3#=TRANSPOSE(AK3#),(AH3#-TRANSPOSE(AL3#))^2)))/(COUNTIF(AG3#,AK3#)-1))

 

[Winston]

Thank you!!! I now see the logic...very ingenious...worked perfectly!