I want to GROUPBY in Excel

0

I have two column Code and Score. Like this

| Code | Score |
|:----:|:-----:|
| M01  | 4     |
| M05  | 3     |
| M02  | 4     |
| M01  | 5     |
| M04  | 4     |
| M01  | 3     |

I want to get the AVERAGE of each score with their respective Code.

Something like this OUTPUT:

| Code | Average |
|:----:|:-------:|
| M01  | 4       |
| M05  | 3       |
| M02  | 4       |
| M04  | 4       |

Thank you for your time and consideration.

Maqsud Inamdar

Posted 2019-11-01T10:22:10.733

Reputation: 71

a pivot table will do this for you – PeterH – 2019-11-01T10:54:42.740

Hey @PeterH, I want to get the COUNTIFS of Score which is >= 4 for each Code. – Maqsud Inamdar – 2019-11-01T10:57:29.000

No problems, use SUMIFS/COUNTIFS. – Akina – 2019-11-01T11:03:52.317

But How do I use COUNTIFS When I want to COUNT the Range: Score whose values are Criteria: >=4. And comes under each category of Code. – Maqsud Inamdar – 2019-11-01T11:06:42.200

@MaqsudInamdar that is not mentioned in your question – PeterH – 2019-11-01T11:33:32.537

@PeterH that was my mistake. Can you help me out here? – Maqsud Inamdar – 2019-11-01T11:45:39.587

Hi @MaqsudInamdar create a new question detailing as much as you can what you need, show some before and after examples – PeterH – 2019-11-01T12:57:03.107

Answers

0

Assuming the input is in column A2:B7 of Sheet1, and the output is in Sheet2. In B2 of sheet2 (beside M01), put :

=SUMPRODUCT((Sheet1!$B$2:$B$7)*(Sheet1!$A$2:$A$7=Sheet2!A2))/COUNTIF(Sheet1!$A$2:$A$7,Sheet2!A2)

idea : using (Sheet1!$A$2:$A$7=Sheet2!A2) to 'generate' a list of 0 & 1, then multiply by the score list, then divide by the number of times the M01 occurred in the defined list.

Please share if it works/not. ( :

p._phidot_

Posted 2019-11-01T10:22:10.733

Reputation: 948

Asked: 2019-11-01T10:22:10.733

Viewed: 42 times

Active: 2019-12-10T07:50:13.107