Summing Question

[cacik05]

I have a project containing 3 columns: Date, Reference No, Count. The reference Number column will contain multiple different reference numbers. A specific reference number may appear in the column several time. I am looking for a formula that will total the counts per reference number. I know this can be done with the SUMIF function, but there should be a way to automatically consolidate the duplicate references numbers and give a total based on all of them.

I am running Excel 2007.

 

[xlhelp]

Ideal way would be sort by Reference Number and use SUBTOTAL command to get your results.

There is also a SUBTOTAL function that you could look up under Help

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I should live a long time - I eat a lot of preservatives.

 

[SkipVought]

hi,

Enter as a Array Formula (ctr+shift+enter):
[tt]
=SUM(IF(FREQUENCY(MATCH(RangeRef,RangeRef,0),MATCH(RangeRef,RangeRef,0))>0,1))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

I recommend a pivottable. That will summarise per reference number regardless of how many times it appears.

Cheers, Glenn.

Beauty is in the eye of the beerholder.