Finding average with one criteria or another.

[buddyhackit9]

I am trying to calculate an average in column E, which will only include numbers that meet one of 2 criteria in column C (either "yes" or "still considering"). Having one criteria is easy, but for some reason I am hung up on adding the second criteria. If I use AverageIFS, it doesn't work because I want it to include the number if it has one or the other, but not have both "yes" or "still considering". I could also use the DAverage as I have a section with the headings "yes" or "still considering", but I am stumped again in coming up with the right formula. Any help is appreciated!!

Thanks.

[Pete_UK]

AVERAGE is made up of the SUM of the cells which match the criteria divided by the count of those cells, so you can use SUMIF and COUNTIF, like this:

=(SUMIF(C:C,"yes",A:A)+SUMIF(C:C,"still considering",A:A))/(COUNTIF(C:C,"yes")+COUNTIF(C:C,"still considering"))

I've assumed that the values to be summed are in column A.

Hope this helps.

Pete

[Fotis1991]

You can use tHis Array formula(CSE)

=AVERAGE(IF(A1:A100="yes",A1:A100="still considering",B1:B100))

[buddyhackit9]

thank you for the help! Pete_UK your formula definitely works so thank you.

Fotis1991, your formula seems very clean and I like it, but I am getting a value error. Is there anything you can see that is slightly out or something?

[Fotis1991]

Hi

Probably you did not use Control+Shift+Enter(Array formula) to confirm. Not just Enter.

But i do not understand how happened...,but this was not correct formila.

This is it(Also, Array formula).

=AVERAGE(IF((A1:A100="still considering")+(A1:A100="yes"),B1:B100))