## AVERAGE based on criteria

The array formula in cell D14 calculates an average based on multiple criteria in cell range B14:B15. If value in column C is equal to B14 or B15 the amount in column D on the same row is included in the average.

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aĀ beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining formula in cell D14

You can't use AVERAGEIF or AVERAGEIFS function in this example, as far as I know. The COUNTIF function allows you to check if at least one out of multiple values are found in a cell range.

COUNTIF(B14:B15,C3:C11)

Note that I am using multiple values in the second argument, theĀ COUNTIF function returns an array that indicates where group A or B is.

{1;1;0;1;1;0;1;1;0}

IF(COUNTIF(B14:B15,C3:C11),D3:D11,"")

The IF function allows you to filter values in column D based on COUNTIF function that serves as a logical expression in this case.

IF({1;1;0;1;1;0;1;1;0},D3:D11,"") and returnsĀ {360;740;"";760;980;"";60;740;""}

AVERAGE(IF(COUNTIF(B14:B15,C3:C11),D3:D11,""))

The AVERAGE function then returns the average from the values in the array ignoring the blanks.

### AVERAGE - AND OR logic

The following array formula calculatesĀ an average based on two conditions, if group is equal to A **OR** B **AND**Ā ID is less than 105 **AND** larger than 100.

There are three logical expressions in the first IF argumentĀ COUNTIF(B14:B15,C3:C11)*(B3:B11<105)*(B3:B11>100).

The asterisk multiplies the arrays, 1 indicates a location where all three logical tests return TRUE and FALSE if at least one returns FALSE.

The array returned from the COUNTIF function is in the first column above,Ā B3:B11<105 is in the second column andĀ B3:B11>100 is in the third column.

The first row: 1*TRUE*TRUE equals 1. All three logical tests are TRUE. The corresponding value is 360 and is included in the AVERAGE calculation.

AVERAGE({360;740;"";760;"";"";"";"";""})

There are two more values that are also in the calculation found in the second and fourth row, 740 and 760.

The average is 620 and is displayed in cell B18.

### Download Excel *.xlsxĀ file

Calculate average of last 10 data with possible blank cells

Question: Answer: This array formula creates a dynamic range, filtering the 10 last data. Adjust cell rangesĀ $A$1:$A$25 in formula below. […]

#N/A error is sometimes used to show gaps in charts, however, the AVERAGE function can't process errors, shown in C11 […]

How to use the AVERAGE function

Calculates the average of numbers in a cell range. In other words, the sum of a group of numbers and […]

The array formula in cell D3 calculates an average and ignores 0 (zeros). =AVERAGE(IF(B3:B8<>0,B3:B8,"")) The formula above is an array […]

The AVERAGE function is designed to ignore blank cells but there are instances where it fails. The picture above seems […]

Column C contains both date and time, to calculateĀ the average of only time we need to extract the hours, minutes […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form