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 for

2.A specific Analyst (ieA4Mary Sue)

3. Error Type is Regulatory (Z:Z)AND

4. 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?

✔ Best Answer

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.MIKE

From what I can understand, it does not appear that =AVERATEIFS() is

what you need.The syntax of the function is similar to SUMIFS

where 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 numbers

and what your expected answer should be.MIKE

A B C1 Susan 500 CRE2 Mary 400 GIB3 Mary 100 BBMass4 Mary 300 Lg Corp5 Mary 700 Lg Corp6 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 - Analyst

B - Quality score per profle

C - Business segment

OK, the =AVERAGEIFS() will work, try this: =AVERAGEIFS(B1:B6,A1:A6,"Mary",C1:C6,"<>GIB",C1:C6,"<>CRE",C1:C6,"<>BBMass")

MIKE

message edited by mmcconaghy

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.

MIKE

message edited by mmcconaghy

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 Regulatory2 Mary 100 BBMass Regulatory3 Mary 500 Lg Corp Regulatory4 Susan 500 IBC Regulatory5 Susan 500 CRE Admi6 Susan 500 CRE Regulatory7 Mary 200 BBMass No ExceptionThe data maybe different but the columns are the same (except the addition of D)

Ok, You see how the formula works,

you add in the final criteria and postyourformula.MIKE

message edited by mmcconaghy

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?

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.MIKE

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!

Yessssss!! I appreciate your help & Thank you!!!!!

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History