# Help with an array formula to average the value in a third column when the condition is TRUE in two

Posted by Tom Bauer on May 03, 2001 9:29 AM

How could I use an array formula to average the value in a third column when the condition is TRUE in two other columns.

Example:

Month Year Total
1 2001 5
1 2001 15
2 2001 8

The average of the Total for Month 1 in the Year 2001 is 10.

Or an alternative:

Date Total
1/17/2001 5
1/20/2001 15
2/5/2001 8

The average for the Total number in January of 2001 is 10.

Any ideas are appreciated.

Posted by Mark W. on May 03, 2001 9:52 AM

Alternative1: {=SUM((A2:A4=1)*(B2:B4=2001)*C2:C4)}
Alternative2: {=SUM((MONTH(A2:A4)=1)*(YEAR(A2:A4)=2001)*B2:B4)}

These are array formulas which must be entered using
Control+Shift+Enter key combination. The braces, {},
are not typed by you.

Posted by Aladin Akyurek on May 03, 2001 10:01 AM

=AVERAGE(IF((MONTH(A3:A5)=1)*(YEAR(A3:A5)=2001),(B3:B5),""))

You can also use 2 cells, one for the month condition and the other for the year condition. You can then substitute cell refs for the constants in the above formula.