Translate from Excel: =AVERAGEIF(DataA,DataB,Condition)

I have this Excel function I need to translate. What it says is Calculate D3:D103 if for the specific cell the value in column A equals 1.

=AVERAGEIF(D3:D103,A3:A103,1)

The D column contains some medians, varying from 1 to 5 with 2 decimals, whereas A is only 1 or 2 (boy or girl).

I saw something with a SUMIF/COUNTIF, but I have to IF for another column’s value!

I am surprised but I too don’t find the AVERAGEIF function in Calc.This could be an enhancement request

I would build the function using AVERAGE (or AVERAGEA) and IF. This should be possible. You would need to look into details of your data.

There is already an enhancement request here and someone is working on it.

this is very good news!!!

I think you can get using a matrix formula:

=AVERAGEIF(D3:D103*(A3:A103=1)*(X3:X103=Z))

Enter the formula with Crl+Shift+Enter.

@mariosv
Just for my curiosity, what is the part (A3:A103=1)(X3:X103=Z) doing?

@ROSt53. Just an error, must be (A3:A103=1)*(X3:X103=Z)

@mariosv Thanks for the * between. However, I don’t get it. I interpret (A3:A103) as the condition that there is a 1 in the cells A3 to A103. Is this correct? But what is the function of (X3:X103)?
Hope you don’t mind my curiosity.

@ROSt53 Forgive me, sometimes it is better goto to bed. The formula is =AVERAGE(D3:D103*(A3:A103=1)). Adding “*(X3:X103=Z)” if you want add a condition for X column and Z value.

@mariosv Thanks for instant reply.
Calc has the AVERAGEIF function already in the AVERAGE function. Great on one side but a problem for all the ones who don’t know that. I would have never tried to add the condition. Hope this will be soon implemented as an AVERAGEIF.

@mariosv Comments are short. With *(X3:X103) you can then add additional conditions. In the above example it would mean IF there is a 1 in A1:A103 AND in X3:X103. Non-consecutive areas. Great functionality.
My curiosity is satisfied. Thanks!