CODEWITHSUNDEEP
excelif-statementaverage
Jason
Jason

Reputation: 75

Excel Average if with multiple criteria

I am expanding on some help I got here yesterday.

I am using the 'average' and 'if' functions as below:

=AVERAGE(IF(YEAR(A:A)=1999,N:N))

I am wanting to also add the condition that if in column F the letters RD appear, the average will be calculated for that year.

Then, I want to calculate the average if the letters RD do NOT appear (i.e. exclude them).

I have tried the AND function but cannot get it to return any results.

TIA.

Upvotes: 2

Views: 1832

Answers (1)

chancea
chancea

Reputation: 5968

Instead of using that array formula. There is a formula that does this for you called AVERAGEIFS. Which can take in multiple criteria to determine what to average. We can add as many criteria as needed to determine our final results.

To test if the date is within the year of 1999 you can use 2 criteria:

AVERAGEIFS(N:N,A:A,">="&DATE(1999,1,1),A:A,"<"&DATE(2000,1,1))

  1. Greater than or equal to the first of the year 1999
  2. Less than the first of the next year 2000

To add the additional requirements you have:

I am wanting to also add the condition that if in column F the letters RD appear

Add the following criteria: *RD* for column F:F:

=AVERAGEIFS(N:N,A:A,">="&DATE(1999,1,1),A:A,"<"&DATE(2000,1,1),F:F,"*RD*")

The * character is a wildcard. So anytext can appear before or after RD.

Now as you also require the opposite then:

Then, I want to calculate the average if the letters RD do NOT appear (i.e. exclude them).

Add the <> (not equals) operator infront of *RD*:

=AVERAGEIFS(N:N,A:A,">="&DATE(1999,1,1),A:A,"<"&DATE(2000,1,1),F:F,"<>*RD*")

Upvotes: 1

Related Questions