# Averaging ''Names'' to columns in a range.

1. ## Averaging ''Names'' to columns in a range.

Afternoon All

I've got a formula that needs solving(ps the one i'm going to post is totally hypothetical)

Basically I want to find a way of averaging each persons number, I know some names are in different columns but i want a way where i can get the name of the person and link it up to the column(the number)

I cannot find a formula that can do this.

Thank you  Register To Reply

2. ## Re: Averaging ''Names'' to columns in a range.

Welcome to the forum. Please fill in the version of Excel that you are using in your forum user profile. Thanks.  Register To Reply

3. ## Re: Averaging ''Names'' to columns in a range.

If your numbers are all 1 then

Formula for E17

=COUNTIF(\$D\$5:\$K\$12,D17)

Otherwise formula for E17 entered using Ctrl Shift Enter

=SUM(IF(\$D\$5:\$K\$12 =D17,\$E\$5:\$L\$12,0))  Register To Reply

4. ## Re: Averaging ''Names'' to columns in a range. Originally Posted by mehmetcik If your numbers are all 1 then

Formula for E17

=COUNTIF(\$D\$5:\$K\$12,D17)

Otherwise formula for E17 entered using Ctrl Shift Enter

=SUM(IF(\$D\$5:\$K\$12 =D17,\$E\$5:\$L\$12,0))
Hi Mate,

thank you but it doesn't work pal.

I'm looking for the average not the sum

Basically the ones I included are more of a test run than an actual document, the document I will be using will potentially have different numbers to represent the names.

I'm looking for the average amount for the names mentioned on the sheet.  Register To Reply

5. ## Re: Averaging ''Names'' to columns in a range.

Formula for E17 Entered using Ctrl Shift Enter

=SUM(IF(\$D\$5:\$K\$12 =D17,\$E\$5:\$L\$12,0))/COUNTIF(\$D\$5:\$J\$12,D17)  Register To Reply

6. ## Re: Averaging ''Names'' to columns in a range. Originally Posted by mehmetcik Formula for E17 Entered using Ctrl Shift Enter

=SUM(IF(\$D\$5:\$K\$12 =D17,\$E\$5:\$L\$12,0))/COUNTIF(\$D\$5:\$J\$12,D17)
Hi mate

Ive tried this and it doesn't work at all.

Just comes up with #value  Register To Reply

7. ## Re: Averaging ''Names'' to columns in a range.

you did not use Ctrl Shift Enter  Register To Reply

8. ## Re: Averaging ''Names'' to columns in a range.

done it, sorted.

Thank you so very much!  Register To Reply

9. ## Re: Averaging ''Names'' to columns in a range.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 