# Solved Is AVERAGEIFS the correct formula to use?

June 28, 2016 at 10:47:22
Specs: Windows 64
 Hi, Back in March DerbyDad03 answered a very similar question - I tried to plug in my data with the formula provided and although the formula worked for the person who posted the ? unfortunately, the formula did not work for me.I need to get an average of the Quality Score per profile based on a few different conditions. All of the individual profile scores are in column AV. The conditions are follows: 1. To get the average of the Quality Score per profile (AV) ONLY for2.A specific Analyst (ie A4 Mary Sue) 3. Error Type is Regulatory (Z:Z) AND4. Exclude (<>) business segment BB Mass, CRE and GIB (all segments are in column G:G)The average will be determined by dividing the (aggregated total ) of the Quality Score per profile (AV) (will be determined by the critera above 2-4) - by the number of profiles - the # of profiles will be determined by the critera above (2-4).Is this even possible?

See More: Is AVERAGEIFS the correct formula to use?

June 28, 2016 at 13:10:57
 Look at the syntax of the formula:AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )Now look at the syntax of your formula:=AVERAGEIFS(D1:D6,"=Regulatory",B1:B6,A1:A6,A2,C1:C6,"<>GIB",C1:C6,"<>CRE",C1:C6,"<>BBMass")What column are you trying Average?I don't think it's Column D.MIKEhttp://www.skeptic.com/

#1
June 28, 2016 at 11:32:12
 From what I can understand, it does not appear that =AVERATEIFS() iswhat you need.The syntax of the function is similar to SUMIFSwhere you have one range to average and multiple criteria.AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )Post a small sample of your data using Column Letters and Row numbersand what your expected answer should be.MIKEhttp://www.skeptic.com/

#2
June 28, 2016 at 11:50:52
 ` A B C ` `1 Susan 500 CRE ` `2 Mary 400 GIB ``3 Mary 100 BBMass ``4 Mary 300 Lg Corp ``5 Mary 700 Lg Corp ``6 Mary 800 IBC ` I need a formula that will give me the average for A:A (Mary only) and the sum of B:B and C:C for all Buiness Segments except CRE, GIB and BBMass (C:C)A - AnalystB - Quality score per profle C - Business segment

#3
June 28, 2016 at 12:13:20
 OK, the =AVERAGEIFS() will work, try this:=AVERAGEIFS(B1:B6,A1:A6,"Mary",C1:C6,"<>GIB",C1:C6,"<>CRE",C1:C6,"<>BBMass")MIKEmessage edited by mmcconaghy

Related Solutions

#4
June 28, 2016 at 12:16:41
 If you do not want to "hard code" the name into the formula, you can reference a cell that contains the name, so the formula:=AVERAGEIFS(B1:B6,A1:A6,A2,C1:C6,"<>GIB",C1:C6,"<>CRE",C1:C6,"<>BBMass")will also work.MIKEmessage edited by mmcconaghy

#5
June 28, 2016 at 12:40:37
 Don't kill me lol - I forgot to add the column D - which I refer to in my initial question as Error Type is Regulatory (Z:Z) That would be the last condition. I only want Regulatory`A B C D ` `1 Mary 400 GIB Regulatory ``2 Mary 100 BBMass Regulatory ``3 Mary 500 Lg Corp Regulatory ``4 Susan 500 IBC Regulatory ``5 Susan 500 CRE Admi ``6 Susan 500 CRE Regulatory ``7 Mary 200 BBMass No Exception `The data maybe different but the columns are the same (except the addition of D)

#6
June 28, 2016 at 12:44:57
 Ok,You see how the formula works, you add in the final criteria and post your formula.MIKEmessage edited by mmcconaghy

#7
June 28, 2016 at 12:59:47
 Fair enough!=AVERAGEIFS(D1:D6,"=Regulatory",B1:B6,A1:A6,A2,C1:C6,"<>GIB",C1:C6,"<>CRE",C1:C6,"<>BBMass")For some reason I get an error message stating that the formula contains an error?

#8
June 28, 2016 at 13:10:57
 Look at the syntax of the formula:AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )Now look at the syntax of your formula:=AVERAGEIFS(D1:D6,"=Regulatory",B1:B6,A1:A6,A2,C1:C6,"<>GIB",C1:C6,"<>CRE",C1:C6,"<>BBMass")What column are you trying Average?I don't think it's Column D.MIKEhttp://www.skeptic.com/

#9
June 28, 2016 at 13:42:55
 And you are correct! I am trying to average the data in column B. =AVERAGEIFS(B1:B6,A1:A6,A2,D1:D6,"=Regulatory",C1:C6,"<>GIB",C1:C6,"<>CRE",C1:C6,"<>BBMass)I am pretty certain I finally got it right!