Excel DSTDEV without using headers in range

Question

Columns A & B contain a sample of data, with shop being an identifier (only two shown here)

I've setup two ranges in columns D & E which I can use the DSTDEV() function in column G

e.g. G2 formula is =DSTDEV($A$1:$B$9,2,D1:E2) and G4 formula is =DSTDEV($A$1:$B$9,2,D4:E5)

so the output currently is:

But in reality I have a tonne of shop identifiers so I wanted to be able to use the DSTDEV() as a flood-fill formula, ideally with an output like so:

Where I could calculate the standard deviation in column E for each shop in column D

I basically wanted DSTDEV() to work like SUMIF() but the criteria has to be a range, and I'm looking for a way round that?!

I thought I would ask before I go creating a UDF to do what I needed!

I tried supplying a split range in the format e.g. (E1,E3) as the criteria but that didn't work

Answer 1

An option would be using Pivot Tables:

Subtotal and total fields in a PivotTable

StDev: An estimate of the standard deviation of a population, where the sample is a subset of the entire population.

This will return exactly the result you want, besides it's really easy to manage and setup

Answer 2

Another option would be use of the STDEV.P function integrated with FILTER. This relies on two premises:

1. STDEV.P calculates the population standard deviation based upon a list of values
2. FILTER can generate a dynamic array of values based on a single criteria when compared against a query list

So a FILTER array would look like this:

Now that the list of individual values have been generated, this can simply be wrapped with the STDEV.P (or any other function based on lists) like this:

From there, you can anchor all elements except for the bold cell, and then flood fill accordingly:

=STDEV.P(FILTER(B2:B9,A2:A9=**D2**,))

Alternatively, it may be useful to include headers and named dynamic ranges via PivotTable, and you could avoid anchoring the formula.