CODEWITHSUNDEEP
excelaverage
user3062448
user3062448

Reputation: 63

Value# error when using averageIFs in Excel

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

enter image description here

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?

Upvotes: 1

Views: 1366

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

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.

enter image description here

Upvotes: 1

Related Questions