Search code examples
excelexcel-formulaworksheet-function

Standard deviation (or sum of squares) of disjoint range

I have a range that I sum over with this formula:

=SUMIFS(sheet!F:F,sheet!A:A,A2,sheet!I:I,"")

That is, there are two conditions: on the other sheet, the value in column A has to match that of A2 here, and the value in column I has to be blank.

I want to find the standard deviation of these values instead. It is enough to find the sum of their squares and use math. I've tried to do this with SUMPRODUCT and array formulas but no luck so far. Any ideas?

If possible I'd like to avoid VBA.

Solution

  • Use this array formula:

    =STDEV(IF((Sheet1!A1:A1000 = A2)*(Sheet1!I1:I1000 = ""),Sheet1!F1:F1000))

    Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.