summing first occurance of values

[TechRetard]

A 1
B 2
B 2
C 3
A 1
C 3
--------
my sum should be 6....first occurance of A, B, C

-cannot sort on anything

Thanks in advance!

[NBVC]

Add a helper column:

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

copied down (assuming data in A1:B6)

Then use: =SUM(C1:C6)

[ChemistB]

Or, in a similar vein;
In helper column
=COUNTIF($A$1:A1,A1)
Result;
=SUMIF($C$1:$C$6,1,$B$1:$B$6)

[TechRetard]

Or, in a similar vein;
In helper column
=COUNTIF($A$1:A1,A1)
Result;
=SUMIF($C$1:$C$6,1,$B$1:$B$6)

any way around helper columns? i have 60k rows...thats a lot of formulas...

[NBVC]

You would need an array formula and 60,000 rows is a lot to process for the array formula... it would be more efficient to use the helper... especially if you have other things going on in the workbook that might add to the resource eating.

[TechRetard]

You would need an array formula and 60,000 rows is a lot to process for the array formula... it would be more efficient to use the helper... especially if you have other things going on in the workbook that might add to the resource eating.

right... yah that would be a lot of checks in the array; alright, thanks guys, appreciate the help

[NBVC]

If you wanted to give it a test, then try:

Please Login or Register  to view this content.

adjust ranges to suit and confirm with CTRL+SHIFT+ENTER

[TechRetard]

If you wanted to give it a test, then try:

Please Login or Register  to view this content.

adjust ranges to suit and confirm with CTRL+SHIFT+ENTER

Seems to work but takes too long, little less than a half a minute....thanks i'll try and work with the helper or try reformatting my data