AVERAGE with OR function

I want to create a formula that says, "if a cell in column D has the phrase "triple net", or if a cell in column D has the word "base" then take the average of the corresponding cells in column M." This is the formula I have come up with but it isn't working... any suggestions? Do I need the OR function?

=IF(OR($D:$D="*triple net",$D:$D="*base*"),AVERAGE($M:$M),"0")

I think you're going to have to do the Sum/Count

=SUM(SUMIF($D:$D,{*triple net","*base*"},$M:$M))/SUM(COUNTIF($D:$D,{*triple net","*base*"}))

Thanks guys, I think I've got it down

Good catch on the missing ", thanks

Should be
=SUM(SUMIF($D:$D,{"*triple net","*base*"},$M:$M))/SUM(COUNTIF($D:$D,{"*triple net","*base*"}))


=AVERAGEIF(D:D,{"*triple net";"*base*"},M:M)
This is only going to include the triple net in the average.

This kind of {or variable} structure works for summing and counting
SUM(SUMIF($D:$D,{*triple net","*base*"},$M:$M))

But you can't use that for an average, as in
AVERAGE(SUMIF($D:$D,{*triple net","*base*"},$M:$M))

However, what is the use of the "curly" brackets and the semicolon? I don't think I've ever used those in Excel before....

The {brackets} create a 'Constant Array'
The semicolon is just a list seperator, can also be a comma.

Since Average = Sum/Count
We need to get the SUM of values in M where D = x OR y - this is the sumif part
Then we need to get the COUNT of times where D = x OR y - this is the countif part

Here's what happens, taking just half of my formula, the sumif part (the countif part works exactly the same, just a count instead of sum)
SUM(SUMIF($D:$D,{"*triple net","*base*"},$M:$M))
The {constant array} essentially makes excel perform 2 versions of the SUMIF.
1 using "*triple net"
the other using "*base*"
It's as if you did this
=SUMIF($D:$D,"*triple net",$M:$M)+SUMIF($D:$D,"*base*",$M:$M)

Thank you. What if I want to add one more condition that I don't want it to average any numbers in column M that equal 0?

We'd have to add that criteria to the count, and use countifS

=SUM(SUMIF($D:$D,{"*triple net","*base*"},$M:$M))/SUM(COUNTIFS($D:$D,{"*triple net","*base*"},$M:$M,"<>0"))

Thank you everyone. I have one final question that I hope isn't too convoluted....

The formula I ended up using was the one Jonmo provided.

Formula:

Please Login or Register  to view this content.

I used this formula to find an expense rate from a set of data. The excel doc I was working in had 7 different sheets with similar sets of data (i.e., same column headings, different numbers within the columns).

I then duplicated Jonmo's formula across all 7 different sheets to find the expense rate for each sheet.

On the final sheet, I want to be able to find the average of these 7 different expense rates. As a general rule of thumb, I know that it isn't correct to just take the averages and divide by the number of averages there are. So is there a way to modify Jonmo's formula (which is the same across all sheets) to find the average of the expense rates of all four sheets?

You're right, you can't really do an average of averages...

What you could do is put individual SUMs for each sheet, and individual COUNTs for each sheet.
The you can SUM all the sums, and divide by the sum of all the counts..

So instead of this in each individual sheet
=SUM(SUMIF($D:$D,{"*triple net","*base*"},$M:$M))/SUM(COUNTIFS($D:$D,{"*triple net","*base*"},$M:$M,"<>0"))

Designate 3 cells in each sheet, one for the sum, one for the count, and one for the average..
Say
J2: =SUM(SUMIF($D:$D,{"*triple net","*base*"},$M:$M))
K2: =SUM(COUNTIFS($D:$D,{"*triple net","*base*"},$M:$M,"<>0"))
L2: =J2/K2

Then on your summary sheet, you can do
A1: =SUM(Sheet1!J2,Sheet2!J2,Sheet3!J3,etc)
B1: =SUM(Sheet1!K2,Sheet2!K2,Sheet3!K3,etc)
C1: =A1/B1

Awesome thanks!

Lastly, there are some worksheets where cells J2 and K2 have zeroes in them because the data that they pulled from did not match up with the criteria of the formula within that cell (which is okay for what I'm doing on my end...almost expected). However, this creates a problem when finding the value in L2 because I get a "#DIV/0!" error message. Because of this error message, it makes it difficult to find the values for my summary sheet.

Is there a way where I can modify the formulas in cells A1, B1, C1 on the summary sheet to just ignore the sheets that have that "#DIV/0!" error message? I only want to be finding the averages for the sheets that actually have values in cells J2, K2, and L2.

Thanks!

I would add IFERROR to the formula in L2 on each sheet..

L2: =IFERROR(J2/K2,0)

Thank you Jonmo...YUGE help

You're welcome