How to sum up only non-repeating values?

**VCK** · 07-30-2009, 07:29 AM

I have data of the following structure:

A 100
A 100
A 100
B 5
B 5
B 2
C 55

I need to get

A 100
B 7
C 55

Meaning that the formula would check for all values in second column which have the same value in first column and them sum up only the ones that don't repeat within this range.

For example in data range with A in first column we have 100, 100 and 100. It is repeating therefore formula takes 100 only once. It produces A 100. For B there are values 5, 5 and 2. 5 gets lost, system provides 7 for B. Etc...

Any ideas how I can make it? There are lots of different values for A, B, C in my real data. Could be 50 different values, could be a hundred.

**DonkeyOte** · 07-30-2009, 07:34 AM

Not really clear what you're trying to do in truth, are you condensing the data or producing an aggregate analysis ?

Assume your sample data were present in range A1:B7 and your values A,B,C in D1:D3 with results to go into E1:E3 ... a simple approach:

C1: =A1&":"&B1
copied down to C7

Results

E1: =SUMPRODUCT(--($A$1:$A$7=$D1),1/COUNTIF($C$1:$C$7,$C$1:$C$7&""),$B$1:$B$7)

**VCK** · 07-30-2009, 07:51 AM

Originally Posted by DonkeyOte

Not really clear what you're trying to do in truth, are you condensing the data or producing an aggregate analysis ?

Actualy I am condensing data.

But the problem I have is that in some cases data I have is repeating. Therefore repeating values should be excluded from subtotals.

I am going to analyse what you are offering now.

**VCK** · 07-30-2009, 08:05 AM

Originally Posted by DonkeyOte

Assume your sample data were present in range A1:B7 and your values A,B,C in D1:D3 with results to go into E1:E3 ... a simple approach:

Let's say my data looks this way:

\1

How the formula look then?

**DonkeyOte** · 07-30-2009, 08:27 AM

I believe I provided the formulae (and references) in post #2, no ?

On an aside it is better generally to post sample workbooks as opposed to images.

**VCK** · 07-30-2009, 08:33 AM

Originally Posted by DonkeyOte

I believe I provided the formulae (and references) in post #2, no ?

On an aside it is better generally to post sample workbooks as opposed to images.

Yes you did. And thank you for that. But you distributed data in other cells and I guess the formula would be different then. If you would figure out the way you distributed data or the way this formula works I would't ask, you know...

**DonkeyOte** · 07-30-2009, 08:38 AM

In all honesty, I don't know what you're asking I'm afraid...

Are you saying you want to delete duplicate rows altogether at source ?
Assuming you do you need to specify one other key fact, is the data sorted by Column A (ie all A's are listed consecutively and so on and so forth through B, C etc...)

**martindwilson** · 07-30-2009, 09:08 AM

donkeyote this seems to work but i cant work out why freq always gives one more element in array so you have to extend sum and other qualifying range by 1
=SUMPRODUCT(--(FREQUENCY(B1:B6,B1:B6)>0),--(A1:A7="b"),B1:B7)
where data is in a1:b6

**DonkeyOte** · 07-30-2009, 05:17 PM

Hi Martin, the Help file on Frequency function explains this (ie returned array > bins array), I don't use Frequency much myself so I wouldn't feel well placed to condone/condemn it's use in this context.