Average if - when matching criteria from 2 columns concurently?

[OnyeI]

Hello All,

Just wondered. Does anyone know if it is possible to calculate the average of a column (D) of numbers - only including the values if the criteria (the word Org1) is present in columns (A) & (B).

I don't mean the usual Averageifs where it will only return a value if the criteria is on the same row in column A and B. It needs to return the average if the criteria in present in either A or B.

I was able to do such a calculation (on my actual speadsheet - looking at one column only, and using IFERROR to get rid of he blanks .....=IFERROR(AVERAGEIF(Data!$K$2:$K$1500,"Org1",Data!$KM$2:$KM$1500),"").....- but can't seem to make headway with 2 columns.

I hope this makes sense?

I attached a mini spreadsheet to try to show what I'm talking about.

Cheers,
Onye

 

[Chihiro]

First of all, I'd suggest cleaning up your data/sample. Column A & Column B has different values (as well A18:A22).

One has space between alpha and numeric, others do not.

Once that's fixed. It's matter of simple calculation.

=(SUMIF($A$3:$A$12,A18,$D$3:$D$12)+SUMIF($B$3:$B$12,A18,$D$3:$D$12))/(COUNTIF($A$3:$A$12,A18)+COUNTIF($B$3:$B$12,A18))

Alternately you can use SUMPRODUCT, but end result would be same.

 

[pecoflyer]

SUMPRODUCT makes it neater =SUMPRODUCT(($A$3:$B$12=$A18)*$D$3:$D$12)/COUNTIF($A$3:$B$12,$A18)

 

[OnyeI]

Thanks Chihiro and pecoflyer for your help with this. And sorry for the long delay in responding. Was on leave then back to pressing work schedule. Have managed to modify your responses to take in additional criteria I overlooked.

But your examples were the foundations. Helped me work out the excel logic that needed to sit under my calculation.

Much appreciated all,
Onye