average multiple criteria

[billandrew]

Hello All

My question this afternoon, Is there an alternate way to average December January & February

The formula I am using is

={SUM(SUMIFS($B$5:$B$16,$A$5:$A$16,{"December","January","February"}))/SUM(COUNTIFS($A$5:$A$16,{"December","January","February"}))}


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]January[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]October[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]November[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]December[/TD]
[TD]32[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

If you make a list of the months, you can do this:

	A	B	C	D	E	F
Months
December
January
February

<tbody>
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]January[/TD]
[TD="bgcolor: #FAFAFA, align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]February[/TD]
[TD="bgcolor: #FAFAFA, align: right"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]31[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]March[/TD]
[TD="bgcolor: #FAFAFA, align: right"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]April[/TD]
[TD="bgcolor: #FAFAFA, align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]May[/TD]
[TD="bgcolor: #FAFAFA, align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]June[/TD]
[TD="bgcolor: #FAFAFA, align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]July[/TD]
[TD="bgcolor: #FAFAFA, align: right"]65[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA"]August[/TD]
[TD="bgcolor: #FAFAFA, align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA"]September[/TD]
[TD="bgcolor: #FAFAFA, align: right"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA"]October[/TD]
[TD="bgcolor: #FAFAFA, align: right"]45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA"]November[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA"]December[/TD]
[TD="bgcolor: #FAFAFA, align: right"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D5[/TH]
[TD="align: left"]=SUM(SUMIFS($B$5:$B$16,$A$5:$A$16,{"December","January","February"}))/SUM(COUNTIFS($A$5:$A$16,{"December","January","February"}))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E5[/TH]
[TD="align: left"]{=AVERAGE(IF(ISNUMBER(MATCH(A5:A16,F5:F7,0)),B5:B16))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E6[/TH]
[TD="align: left"]{=AVERAGE(IF(ISNUMBER(MATCH(A5:A16,{"December","January","February"},0)),B5:B16))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



You can make an external list, or internal to the formula.

 

[johnmpl]

Hi, to both of you!

Another options could be:

Book1
	A	B	C	D	E	F
5	January	25		31	31	December
6	February	36				January
7	March	35				February
8	April	24
9	May	15
10	June	18
11	July	65
12	August	25
13	September	36
14	October	45
15	November	12
16	December	32
Hoja1

Cell Formulas
Range	Formula
D5	=AVERAGE(SUMIF(A5:A16,{"December";"January";"February"},B5:B16))
E5	{=AVERAGE(IF(COUNTIF(F5:F7,A5:A16),B5:B16))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Blessings!

 

[billandrew]

Both worked

Thank You Eric & John....