# Averaging rents based on two criteria

1. ## Averaging rents based on two criteria

Hi all,

I have a specific use case I am trying to build a formula for. I am thinking it might include some combination of XLOOKUP and INDEX MATCH MATCH, both of which I've used before, but I this is beyond my pay grade and I am struggling to figure out a solution (it might include neither of those functions).

Attached is a sample file.

The formulas are to go in cells Q2:Q6 (highlighted green). My objective is as follows:

Q3 would be the average of B3:L8 IF P3 matches the corresponding number in B2:L2 AND IF S2 matches the corresponding letter in column M. So if the "Subject" (S2) has a B "impr. rating", I am trying to have it calculate the average rent of 50 sq ft unit sizes (column B represents market rents for 50 sq ft units, and Q3 is adjacent to 50 sq ft in column P.

You'll notice some of the columns don't have data, so I imagine an IFERROR would need to be incorporated into here somehow.

As I'm typing it out I realize it probably sounds confusing. But hopefully viewing the file provides clarity.

Any help would be appreciated!

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #1 requires good titles. "Need Help with Specific Formula, Might Include XLOOKUP and/or INDEX MATCH MATCH" assumes the solution and does not describe your question. I have updated it for you this time because you are a new member. --6StringJazzer

2. ## Re: Averaging rents based on two criteria

Use this formula in Q2 and copy down:

Formula:
`Please Login or Register  to view this content.`

3. ## Re: Averaging rents based on two criteria

Try this
In Q2
``Please Login or Register  to view this content.``

4. ## Re: Averaging rents based on two criteria

Originally Posted by 6StringJazzer
Use this formula in Q2 and copy down:

Formula:
`Please Login or Register  to view this content.`
Sorry, this is the sum, not an average. Looks like kvsrinivasamurthy has the complete solution.

5. ## Re: Averaging rents based on two criteria

Cell Q2 Array formula , Drag down

HTML Code:
``=IFERROR(AVERAGE(IF((\$M\$3:\$M\$8=\$S\$2)*(\$B\$2:\$L\$2=P2)*(\$B\$3:\$L\$8<>""),\$B\$3:\$L\$8)),"")``

6. ## Re: Averaging rents based on two criteria

kvsrinivasamurthy provided exactly what I was looking for. Amazing, thank you!

7. ## Re: Averaging rents based on two criteria

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.

8. ## Re: Averaging rents based on two criteria

wk9128's formula should also work. Like what wk9128 said, it's an array formula. Ctrl+Shift+Enter to enter the formula.

There are currently 1 users browsing this thread. (0 members and 1 guests)