CODEWITHSUNDEEP
excelexcel-formula
Zion
Zion

Reputation: 1

Averageifs for multiple criteria

Why am I getting #DIV/0! using the fuction in the picture? I am trying to get the average of (B2:B11) where A2:A11 equals hostcareers and A2:A11 equals hosttalent.

Hostname function: enter image description here

Upvotes: 0

Views: 481

Answers (1)

Chris Strickland
Chris Strickland

Reputation: 3490

Ignore AVERAGEIFS and do it the old fashioned way, from before AVERAGEIFS existed:

=AVERAGE(IF((A2:A11="hostcareers")+(A2:A11="hosttalent"), B2:B11))

You generate two arrays based on the conditions and add them:

(A2:A11="hostcareers")+(A2:A11="hosttalent")

The two results sets will be an array of TRUE/FALSE values where the source range matches the condition. Adding them is the equivalent of using OR*. Pass that to IF with the data range, and you'll get an array containing all the corresponding numbers from the data range or FALSE if neither conditions was met.

IF((A2:A11="hostcareers")+(A2:A11="hosttalent"), B2:B11)

Pass that to AVERAGE and you've got it.

* if you wanted to simulate AND, you'd multiply them.

Upvotes: 1

Related Questions