CODEWITHSUNDEEP
excelaveragecriteria
Jason
Jason

Reputation: 75

Excel averageifs with or function

I am using the averageifs function, and have one column where I need to calculate the average if either of the criteria are true.

I have tried using the OR function, and tried the curly brackets, but both give me errors.

=AVERAGEIFS(N1:N612,I1:I612,{"FL","IF"})

There are more ranges, but the coding for those is fine.

To be clear, I want to return an average if column "I" contains (specifically) the letters FL, OR the letters IF. Any other letters should mean that entry is not averaged.

TIA!

Upvotes: 5

Views: 79920

Answers (5)

Scott Davies
Scott Davies

Reputation: 103

A solution to your question is:

=AVERAGE(Maxifs(N1:N612,I1:I612,{"FL";"IF"}))

Upvotes: 0

Sebastian Strauss
Sebastian Strauss

Reputation: 1

Another way to solve this would be to create an additional column (let's call it column J) that returns TRUE if column I contains either "FL" or "IF":

J1 = IF(OR(I1="FL",I1="IF"),TRUE,FALSE)

Then you can reference that column in your AVERAGEIFS formula instead:

=AVERAGEIFS(N1:N612,J1:JI612,TRUE)

Upvotes: 0

Andrew Nelson
Andrew Nelson

Reputation: 21

Do not think Excel has this feature, but in Google Sheets, I've been using the below to achieve similar results

=average(filter($A:$A,($C:$C=$E$6)+($C:$C=$E$7)))

giving me an average of the values in A:A where C:C matches the value in either E6 or E7

unlike an ifs / sumifs etc., the filter uses =, not a comma, thus $C:$C=$E$6 is used, not $C:$C,$E$6

Mathematical symbols are just entered ( so <> rather than "<>"& )

Upvotes: 2

gtwebb
gtwebb

Reputation: 3011

Chancea has given some good techniques I will list one more that avoids array formulas as many will find it easier to read avoiding the sumproduct.

=(SUMIF(I1:I612,"FL",N1:N612)+SUMIF(I1:I612,"IF",N1:N612))/(COUNTIF(I1:I612,"FL")+COUNTIF(I1:I612,"IF"))

Upvotes: 6

chancea
chancea

Reputation: 5958

AVERAGEIFS extra criteria is a boolean AND operator, it can't perform OR operations. But since your columns do not change we can somewhat implement this ourselves:

Here is a simple way using an array formula (entered with ctrl + shift + enter):

=AVERAGE(IF((I1:I612="IF")+(I1:I612="FL"),N1:N612))

Or if you don't like using array formulas you basically do this manually using SUMPRODUCT and COUNTIF:

=SUMPRODUCT((I1:I612="IF")*(N1:N612)+(I1:I612="FL")*(N1:N612))/(COUNTIF(I1:I612,"IF")+COUNTIF(I1:I612,"FL"))

Upvotes: 10

Related Questions