Value# error when using averageIFs in Excel

Question

I am getting the following error VALUE# when I try to use the AVERAGEIFS function on the following data:

Below is my AVERAGEIFS function:

  =AVERAGEIFS(D2:H9,A:C,"*Male*",A:C,"*Artist*")

Could you please advise on why I am getting this error?

Scott Craner · Accepted Answer · 2018-02-16 14:42:04Z

1

AVERAGEIFS() requires that all the ranges must be the same size and shape. You are comparing three whole columns to a range 5 columns wide and 8 rows high.

Use SUMPRODUCT:

=SUMPRODUCT(ISNUMBER(SEARCH(" Male "," " & B2:B9 & " "))*ISNUMBER(SEARCH("Artist",C2:C9))*D2:H9)/SUMPRODUCT(ISNUMBER(SEARCH(" Male "," " & B2:B9 & " "))*ISNUMBER(SEARCH("Artist",C2:C9))*(D2:H9>0))

Note the spaces around " Male " other wise it will pick up the male in Female.

edited Feb 16, 2018 at 14:42

answered Feb 16, 2018 at 13:54

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3062448 Over a year ago

Hello thank you for your help :) however this is not what I mean. I mean the average of score so the answer should actually be 3+2+3+1+3+6+8+1+4+7+4+5+4 divided by 13 which would 3.9 :)? is there a way to do this

Scott Craner Over a year ago

@user3062448 see edit. please mark as correct if this works for you by clicking the check mark by the answer. Also, Next time please include as much information in the original question, which should include expected output.

Collectives™ on Stack Overflow

Value# error when using averageIFs in Excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related