What's the equivalent averageifs for standard deviation?

3
votes

I have asked aboutand already got answer about averageifs excel function here. However, I was also wondering if I can get standard deviation for the same data set in new column. Here is the screenshot of sample data:

1
excelexcel-formulastandard-deviation

1 Answers

5
votes

You can use an array formula to first filter through your rows and then apply the standard deviation to the sample. Formula would look like this:

=STDEV.S(IF((IF(C:C="alpha",1,0)*IF(D:D="S14",1,0))=1,B:B,""))

Inputted using CTRL + SHIFT + ENTER. Cheers,

As provided by @ScottCraner in the comments the much improved formula:

=STDEV.S(IF((C:C="alpha")*(D:D="S14"),B:B))