Average with multiple criteia in same column

[jdurand02]

I need to find the average of a value based on multiple values in multiple columns.

=AVERAGE(IF(Data!D:D="Smith", If(Data!G:G="N/A", If(Data!G:G="TP", If(Data!H:H="Standard", If(Data!H:H="Consulting", If(Data!H:H="Multi_Consulting", If(Data!H:H="Standard_MP", If(Data!H:H="Multi_MP", If(Data!H:H="Multi", If(Data!K:K="8",Data!T:T)))))))))))

Basically if column D = Smith and
Column K = 8 and
Column G = N/A OR TP and
Column H = Standard OR Consulting OR Multi_Consulting OR Standard_MP OR Multi_MP OR Multi

Columns G and H can have a variety of combinations (so basically the values in Column H will have either a N/A or TP in column G).

Please let me know if this does not make sense. I have tried quite a few variations inclusing SumProduct and AverageIfs and nothing seems to work for what I need.

Thanks.

 

[Michael M]

Don't have EXcel at the mo', but try

=AVERAGE(IF(AND(Data!D:D="Smith", Data!K:K="8",Data!G:G="N/A"), IF(OR(Data!G:G="TP", Data!H:H="Standard", Data!H:H="Consulting", Data!H:H="Multi_Consulting",Data!H:H="Standard_MP", Data!H:H="Multi_MP",Data!H:H="Multi"),Data!T:T)))

 

[njimack]

Welcome to the board. Is this what you're after?
=AVERAGE(IF(Data!D:D="Smith",IF(OR(Data!G:G={"N/A","TP"}),IF(OR(Data!H:H={"Standard","Consulting","Multi_Consulting","Standard_MP","Multi_MP","Multi"}),IF(Data!K:K="8",Data!T:T)))))

Note that referring to an entire column is inadvisable - if the size of your data is variable, consider dynamic ranges.

 

[circledchicken]

Hi and welcome to the forum,

Also untested, but for Excel 2007+ maybe:

=SUM(SUMIFS(
       Data!T:T,
         Data!D:D,"Smith",
         Data!G:G,{"N/A","TP"},
         Data!H:H,{"Standard","Consulting","Multi_Consulting","Standard_MP","Multi_MP","Multi"},
         Data!K:K,"8"))/
SUM(COUNTIFS(
         Data!D:D,"Smith",
         Data!G:G,{"N/A","TP"},
         Data!H:H,{"Standard","Consulting","Multi_Consulting","Standard_MP","Multi_MP","Multi"},
         Data!K:K,"8"))

 

[jdurand02]

Thank you, but that did not work. I even made my ranges dynamic but it only returns a 0. I have checked and everything is pointing where it needs to. I am pretty fluent in Excel and advanced formulas, I just cannot figure out why this one is giving me such a hard time.

 

[circledchicken]

For posts #2 and #3 you need to press CTRL-SHIFT-ENTER rather than just ENTER as they are array formulae. See:
Introducing array formulas in Excel - Excel - Office.com
Excel Array Formulas Beg - Advanced - YouTube
Array Formulas

 

[jdurand02]

Still no luck making the formulas an array. I am trying to figure out how to attach a sample document, but not having much luck.

 

[circledchicken]

You can't attach a document but you can post a link to Box.com or Dropbox or Sydrive etc.

 

[Aladin Akyurek]

I need to find the average of a value based on multiple values in multiple columns.

=AVERAGE(IF(Data!D:D="Smith", If(Data!G:G="N/A", If(Data!G:G="TP", If(Data!H:H="Standard", If(Data!H:H="Consulting", If(Data!H:H="Multi_Consulting", If(Data!H:H="Standard_MP", If(Data!H:H="Multi_MP", If(Data!H:H="Multi", If(Data!K:K="8",Data!T:T)))))))))))

Basically if column D = Smith and
Column K = 8 and
Column G = N/A OR TP and
Column H = Standard OR Consulting OR Multi_Consulting OR Standard_MP OR Multi_MP OR Multi

Columns G and H can have a variety of combinations (so basically the values in Column H will have either a N/A or TP in column G).

Please let me know if this does not make sense. I have tried quite a few variations inclusing SumProduct and AverageIfs and nothing seems to work for what I need.

Thanks.

Control+shift+enter, not just enter:

=AVERAGE(
  IF(Data!$D$2:$D$400="Smith",
  IF(ISNUMBER(MATCH(Data!$G$2:$G$400,{"N/A","TP"},0)), 
  IF(ISNUMBER(MATCH(Data!$H$2:$H$400,
       {"Standard","Consulting","Multi_Consulting",
        "Standard_MP","Multi_MP","Multi"},0)),
  IF(Data!$K$2:$K$400=8,
    Data!$T$2:$T$400)))))

If the range in column K is really text, modify 8 as "8". See also Post #4 for a 2007 system or later.

 

[circledchicken]

If the range in column K is really text, modify 8 as "8". See also Post #4 for a 2007 system or later.

barry houdini has very kindly pointed out why post #4 won't work, so I thought I would share it here:

I replied to the thread above but my answer was almost identical to Aladin's so I deleted -

Re your version - that's a viable option but in order for all combinations for columns G and H to be considered you need to separate one of these two

{"Standard","Consulting","Multi_Consulting","Standard_MP","Multi_MP","Multi"}

{"N/A","TP"}

....with commas and one with semi-colons in each of the SUMIFS/COUNTIFS functions (thus giving a 2x6 matrix of results which is then summed)

Note that Aladin's version is more easily "extensible" - if you have 3 or more multi-condition criteria then the SUMIFS/COUNTIFS option can't work

regards, barry

Here is the corrected version but as barry has stated, Aladin's version is more flexible:

=SUM(SUMIFS(
       Data!T:T,
         Data!D:D,"Smith",
         Data!G:G,{"N/A";"TP"},
         Data!H:H,{"Standard","Consulting","Multi_Consulting","Standard_MP","Multi_MP","Multi"},
         Data!K:K,8))/
SUM(COUNTIFS(
         Data!D:D,"Smith",
         Data!G:G,{"N/A";"TP"},
         Data!H:H,{"Standard","Consulting","Multi_Consulting","Standard_MP","Multi_MP","Multi"},
         Data!K:K,8))