Aveageif containing blank value => return #VALUE!

[garga]

Hi guys,

After spending couple of hours trying to find a solution to my problem, I ask for the first time to professionals

My data

Team 9/05/2016 9/05/2016 10/05/2016 13/05/2016 14/05/2016 16/05/2016
Ben
Ben 3.00
Ben 1.12
Ben 1.08
Michel 1.08
Ben 0.49

My formula : =AVERAGEIFS(B2:G7,A2:A7,"Ben")

I presume it is linked to the fact I have empty cells but I can't fix it and it's driving me crazy !!

Please help !

Thanks and respect in advance to my hero !
Cyrille

 

[Marcelo Branco]

If you have gotten a #DIV/0! result probably there are extraneous characters (spaces) in A2:A7.

See if this array formula works
B10
=AVERAGE(IF(TRIM(A2:A7)=A10,IF(ISNUMBER(B2:G7),B2:G7)))
Ctrl+Shift+Enter

M.

 

[garga]

If you have gotten a #DIV/0! result probably there are extraneous characters (spaces) in A2:A7.

See if this array formula works
B10
=AVERAGE(IF(TRIM(A2:A7)=A10,IF(ISNUMBER(B2:G7),B2:G7)))
Ctrl+Shift+Enter

M.

Thanks Marceloooooooooo

The Trim solved it !!!!!!!!!!

Owe you a beer !