Using Averageif for 2 criteria in an Array in Excel

0
votes

I'm trying to use the Averageif(s) formula for an array with two criteria for data that is in columnar form, with skill (i.e, SAP, column C), company (i.e, Facebook, column D), and rate (i.e, $25, column E).

I'd like the find the average rate for each skill with the following criteria:

  1. the averageif formula will ignore 0s so as to not skew the average
  2. the averageif formula will take the average of each skill.

So far I've tried =AVERAGEIF($C$7:$C$138,C7=$C$7:$C$138,$E$7:$E$138) but it is showing a #DIV/0 error.

How do I incorporate criteria #1 into the formula?
Thank you!

1
arraysexcel
It would be easier if you provided some example of your input data and expected output, so we don't have to reproduce your whole table from 0. Ps.: have you tried using a pivot table? - dot.Py
Sure example data: SAP, FB, $25 (row 1), SAP, Google, $23 (row 2), SAP, Oracle, $0 or blank (row 3). Expected result (average of FB and Google rates, ignoring the 0): $24 for the SAP skillset among a group of companies. - Meg
To incorporate if it's not 0, it'd be for example =AverageIf($E$7:$E$138,"<>0"). Also could you kindly post some sample data in to your original post? Thanks! - BruceWayne

1 Answers

2
votes

Try AVERAGEIFS like this

=AVERAGEIFS($E$7:$E$138,$E$7:$E$138,"<>0",$C$7:$C$138,C7)