Excel - Averege value in column when cells contains certain text but average without zeros

2
votes

I'm trying to get average up the row of number when a cell in the same row contains text "mobile" with this formula =AVERAGEIF(A:A,"*mobile",B:B) but I don't get good value because zeros in Column B.

[Column A - Website]
Blic - computer
Blic - mobile
b92 - computer
b92 - mobile
politika - computer
politika - mobile

[Column B - Cost]
$5.00
0
0
$13.00
$20.00
$17.00

Average - computer $8.33
Average - mobile $10.00
Average - total $9.17

How to exclude zeros?

Thanks,

1
excelaverage

1 Answers

3
votes

Instead of =AverageIf() use the slightly more powerful =AverageIfs() so you can add more criteria:

=AVERAGEIFS(B:B, A:A,"*mobile",B:B, "<>0")