how to use multiple criteria in AVERAGEIF function

Question

I want to calculate the average buy price of stocks:

for the average column I have the following formula: =AVERAGEIF(C:C,C2,H:H)

But that calculates it for all. I'm trying to add a second criteria, I tried

=AVERAGEIF(C:C,C2 AND G2="Buy",H:H)

but it returns an error. How do I chain criterias?

serdar · Accepted Answer · 2020-02-08 16:31:13Z

1

You can use AVERAGEIFS formula for multiple conditions. Just keep in mind that parameter orders are slightly different from AVERAGEIF.

Your usage may be,

=AVERAGEIFS(H:H, C:C, C2, G:G, "Buy")

answered Feb 8, 2020 at 16:13

serdar

1,6271 gold badge21 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

basic · Accepted Answer · 2020-02-08 16:28:47Z

1

You can also use the AVERAGE array function with IF included:

{=AVERAGE(IF((C2=C:C)*("Buy"=G:G);H:H))}

Array formula after editing is confirmed by pressing ctrl + shift + enter

answered Feb 8, 2020 at 16:28

basic

12k2 gold badges15 silver badges29 bronze badges