Multi column if statement - Excel

I am trying to find a formula to check conditions in two different columns and then calculate the average of the corresponding values. The following example reflects what I would like to achieve. Let's say I want to find the average of North-western cities.

I already identified the average of the western cities with an array function like this:

=AVERAGE(IF($C$3:$C$10={"LA","SF","POR","SEA"},$D$3:$D$10))

Then hit <kbd>Ctrl</kbd> + <kbd>Shift</kbd> + <kbd>Enter</kbd>

Because of how my data is organized, this previous formula is the only way I will be able to determine (manually somewhat) whether a city is located on the east or the west coast.

Now I'm missing the part that would say "And also if the previous column says 'north' "

Update with Array Function

First Attempt:

So at first I had thought you could just add a second condition to the Array Function like this:

=AVERAGE(IF(AND($C$2:$C$9={"LA","SF","POR","SEA"},$B$2:$B$9={"North"}),$D$2:$D$9))

Where the AND would require each item it be true, but this appears not to have worked... and array functions are tricky beasts to debug.

Second Attempt:

Instead, what you can do is extend the IF function with a second IF. So in the event that the first one is true, the value should return another IF that checks a second criteria. Only if it meets both criteria, return the score, otherwise it will still return nothing , which won't get picked up by the average function.

So the final thing should look like this:

=AVERAGE(IF($C$2:$C$9={"LA","SF","POR","SEA"},IF($B$2:$B$9={"North"},$D$2:$D$9)))

Named Array

Since you are unable to create a column to store the data related to which cities live in the west, it would be best if you had some way to easily update this information instead of hardcoding it throughout your functions. One thing you can do to is define a name to reference later.

Just go to Formulas Ribbon > Defined Names Section > Name Manager > New

Then create the array of western city values as a named parameter to be used later

Then you can use it in a formula in place of the array like this:

=AVERAGE(IF(C2:C9=WestCities,IF(B2:B9="North",D2:D9)))

With PivotTable

Here's my suggestion:

First, add a column for East/West, so you're not stuffing data into a formula

Then add a pivot table and it will do all you calculations for you.

Set it up like this (make sure to aggregate by Average (not sum):

And it will look like this:

If you want to keep your original formatting, you can just run the formulas off of your pivot table like this:

=GETPIVOTDATA("Score",Sheet4!$A$3,"E/W","West","S/N","North")

With West Column

Also, if you had the ability to add in a column for west, you could easily check this with AVERAGEIFS with multiple criteria like this:

=AVERAGEIFS(D2:D9,A2:A9,"West",B2:B9,"North")