how to use multiple criteria in AVERAGEIF function

1
votes

I want to calculate the average buy price of stocks:

enter image description here

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?

2
excelexcel-formulaaverage
Check AverageIFS - Ricardo Diaz

2 Answers

1
votes

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

enter image description here

1
votes

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")