# Perform functions (AVERAGE, STDEV) on rows grouped by unique values in Google Sheets

I would like to be able to group rows based on unique values and then output the `AVERAGE` and `STDEV` values for those groups.

Using the table below as an example, I would like to determine the `AVERAGE` and `STDEV` for each unique ID and type (1a, 1b, 2a, 2b, 3a, 3b).

One possible solution is to create a new column and use `CONCATENATE` to combine the ID and Type together to create unique IDs to work from, but I'd like to try to avoid extraneous columns if possible.

I feel like this could be accomplished using some combination of the `QUERY`, `UNIQUE`, and `FILTER` functions, but I haven't been able to figure it out.

``````1   a   5
1   a   7
1   a   9
1   b   2
1   b   4
1   a   6
1   a   9
1   b   8
1   a   1
2   a   5
2   a   2
2   a   4
2   b   6
2   b   7
2   a   8
3   b   1
3   b   7
3   a   5
3   a   6
3   a   8
3   a   9
3   b   4
3   b   6
3   b   3
``````

## 1 Answer

``````={QUERY(A1:C, "select A,avg(C)
where A is not null
group by A
label avg(C)'Averages'", 0);
QUERY(A1:C, "select B,avg(C)
where B is not null
group by B
label avg(C)''", 0)}`````` ``````={"Stdev";
STDEV(FILTER(C:C, A:A=E2));
STDEV(FILTER(C:C, A:A=E3));
STDEV(FILTER(C:C, A:A=E4));
STDEV(FILTER(C:C, B:B=E5));
STDEV(FILTER(C:C, B:B=E6))}`````` ## ______________________________________________________________

``````=ARRAYFORMULA(QUERY({A1:A&B1:B, C1:C},
"select Col1,avg(Col2)
where Col1 is not null
group by Col1
label avg(Col2)'Averages'", 0))`````` ``````={"Stdev";
STDEV(FILTER(C:C, A:A&B:B=E2));
STDEV(FILTER(C:C, A:A&B:B=E3));
STDEV(FILTER(C:C, A:A&B:B=E4));
STDEV(FILTER(C:C, A:A&B:B=E5));
STDEV(FILTER(C:C, A:A&B:B=E6));
STDEV(FILTER(C:C, A:A&B:B=E7))}`````` • For the Stdev filtering portions, does each unique ID need to be entered manually? The example only runs from E2 to E7, so copying duplicating the line and editing in new row number isn't too difficult, but using the `QUERY` formula you provided on my actual data set results in 104 unique IDs, and the list will be growing. I can edit line by line, but again, if there is a simpler and less error prone way to do it, I'd appreciate it. – Corey Jun 6 '19 at 3:40
• you can use `stdev` individually and drag down the formula: i.stack.imgur.com/BfKue.png - `=IFERROR(STDEV(FILTER(C:C, A:A&B:B=D3)))` – user0 Jun 6 '19 at 6:16