Calculating average value, only counting duplicate rows once

Question

I have an Excel spreadsheet with 2 Name?Value columns, one with a Name and one with a Number.  These 2 combination of columns exist across the entire spreadsheet in different locations and I need to calculate the average value for the Number columns but only counting the duplicate entries once, e.g. 

A1: John

B1: 15

A2: Pete

B2: 12

E1: Sarah

F1: 7

E2: John

F2: 15

A6: Alex

B6: 5

In this scenario, I need to find the average value for the entries, only counting John once.  How can I do this through formulas?

Cheers.

Answer 1

JigBhakta,

As long as cells A3:C5 are empty then you can use these formulas instead of the ones given previously.

In C1 have this formula and drag-fill down to C6:

=IF(A1="","",IF(COUNTIF(A$1:A1,A1)>1,0,1))

In G1 have this formula and drag-fill down to G2:

=IF(ISERROR(MATCH(E1,A$1:A$6,0)),IF(E1="","",IF(COUNTIF(E$1:E1,E1)>1,0,1)),0)

Then the formula for the average is:

=(SUMIF(C1:C6,1,B1:B6)+SUMIF(G1:G2,1,F1:F2))/SUM(C1:C6,G1:G2)

Please adjust the ranges to suit where you data really is.

________________________

Regards, Tom

Answer 2

Ashish,

Your formula doesn't seem to be producing a correct result for the average where duplicates are only counted once.

If the dataa was names in A1:A5 (John, Pete, Sarah, John, Alex) & ages in B1:B5 (15, 12, 7, 15, 5), then the average required by the user would be (15+12+7+5)/4 = 9.75, but your formula is returning 13.5

Also, this 5-pair set of data isn't in A1:B5, it's in A1:B2, A6:B6 and E1:F2

It's a tricky one!

_________________

Regards, Tom

Answer 3

JigBhakta,

If you can make do with helper columns, then you could try this approach.

Say you have names in A1:A5 (John, Pete, Sarah, John, Alex) & ages in B1:B5 (15, 12, 7, 15, 5).

Then in C1 have this formula, and drag-fill down to C5:

=IF(COUNTIF(A$1:A1,A$1:A1)>1,0,1)

If your average is to be calculated in say C7, then have this formula there:

=SUMIF(C1:C5,1,B1:B5)/SUM(C1:C5)

Adjust the ranges to suit where your data really is.

***** EDIT *****

Alternatively, if you can't/prefer not to use helper columns, then try this array formula for the average (has to be confirmed with CTRL+SHIFT+ENTER, not just ENTER):

=SUMPRODUCT((IF(COUNTIF(INDIRECT("A1:A" & ROW(A1:A5)),A1:A5)>1,0,1))*(B1:B5))/SUM(IF(COUNTIF(INDIRECT("A1:A" & ROW(A1:A5)),A1:A5)>1,0,1))

_______________________

Regards, Tom

Answer 4

Also, using the array gives me a much larger number than if I use the Helper columns (which I would like to avoid)

Answer 5

Thank Tom.  This works.  However, can the same be applied if the name/value pairs are not in the same 2 columns, i.e. if they are like the example I provided?