Standard deviation based on a criteria in excel

Question

I want to replicate =averageif but for standard deviation and not having much luck

=STDEV(IF(Sheet1!$C:$C,"6",Sheet1!$AL:$AL))

Where column C = the criteria I'm searching for eg: 6 and AL is the column where I want it to calculate the std dev based on

Answer 1

My simulated data are in D2:E439.

In the latest version of Excel, you can use the FILTER function to return an array to pass to the STDEV.S function:

=STDEV.S(FILTER($D$2:$D$439,$E$2:$E$439=6))

In older versions, use this (entered using Ctrl+Shift+Enter):

{=STDEV(IF($E$2:$E$439=6,$D$2:$D$439,""))}