# conditional average - simple

#### morag

##### New Member
Should be fairly simple - but I'm having a mental blank today.

Two columns:

ColA ColB

red 3
blue 2
yellow 3
red 2
red 4

How do I calculate the average count for each colour (red should be 3) - I've got hundreds of different colours?

Thanks.

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### VoG

##### Legend
Try

=SUMIF(A1:A100,"red",B1:B100)/COUNTIF(A1:A100,"red")

Edit:

Or like this if you have many colours:

<b>Sheet4</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:32px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >red</td><td style="text-align:right; ">3</td><td > </td><td >red</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >blue</td><td style="text-align:right; ">2</td><td > </td><td >blue</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >yellow</td><td style="text-align:right; ">3</td><td > </td><td >yellow</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >red</td><td style="text-align:right; ">2</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >red</td><td style="text-align:right; ">4</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E1</td><td >=SUMIF(A1:A100,D1,B1:B100)/COUNTIF(A1:A100,D1)</td></tr><tr><td >E2</td><td >=SUMIF(A2:A101,D2,B2:B101)/COUNTIF(A2:A101,D2)</td></tr><tr><td >E3</td><td >=SUMIF(A3:A102,D3,B3:B102)/COUNTIF(A3:A102,D3)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

Last edited:

#### orbea_adam

##### Well-known Member
=AVERAGE(IF((ColA)="red",ColB))

confirmed with ctrl+shift+enter
Adam

#### fecurtis

##### Board Regular
Using the sumif function. So lets say your data range is from cells A1:B200 and the colors are in column A and the numbers are in column B.

Get the sum of the numbers for each color:
=SUMIF(A1:B200, "red",B1:B200)
Do that for each color.

Get the sum of all of the values:
=SUM(B1:B200)

Put them together to get the average, so to get the average of all colors marked "red", use:
=(SUMIF(A1:B200, "red",B1:B200))/(SUM(B1:B200))

#### orbea_adam

##### Well-known Member
fecurtis, I don't understand how your solution gives the average for red. Am I missing something? New math? Adam

Using the sumif function. So lets say your data range is from cells A1:B200 and the colors are in column A and the numbers are in column B.

Get the sum of the numbers for each color:
=SUMIF(A1:B200, "red",B1:B200)
Do that for each color.

Get the sum of all of the values:
=SUM(B1:B200)

Put them together to get the average, so to get the average of all colors marked "red", use:
=(SUMIF(A1:B200, "red",B1:B200))/(SUM(B1:B200))

Replies
8
Views
502
Replies
1
Views
473
Replies
3
Views
455
Replies
10
Views
577
Replies
5
Views
132

Threads
1,191,174
Messages
5,985,107
Members
439,940
Latest member
Kyrad42

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back