Summarised list from long list
 

Morning,

In cells B2 to B14 I have got three seperate reference numbers i.e. 1740,
1741 & 1742. For example in cells B2, B4, B8, B9 & B10 there would be 1740,
then in cells B3, B7 and B11 there would be 1741. 1742 would be in B5, B6,
B12, B13 & B14.
Now in cells C2 to C14 I have got volumes relating the the respective
reference.

I want to create a list (in cell E2 to E5) just listing the three reference
numbers then summing up the volumes associated to the reference number.

I do not want to type in the reference numbers, I would like a formula to
pull the three reference numbers on the list and then I would use sumif to
add up the volumes per reference number.

Thanks

Summarised list from long list
 

If i've understood correctly you want to extract the unique value in your
range B2:B11. Try this in E2 and drag down. It's an array formula so
Ctrl+Shift+enter

=IF(COUNTIF($B$2:B2,B2)=1,B2,"")

It will leave a space so delete the cell and shift cells up.

Mike

"Sunnyskies" wrote:

Morning,

In cells B2 to B14 I have got three seperate reference numbers i.e. 1740,
1741 & 1742. For example in cells B2, B4, B8, B9 & B10 there would be 1740,
then in cells B3, B7 and B11 there would be 1741. 1742 would be in B5, B6,
B12, B13 & B14.
Now in cells C2 to C14 I have got volumes relating the the respective
reference.

I want to create a list (in cell E2 to E5) just listing the three reference
numbers then summing up the volumes associated to the reference number.

I do not want to type in the reference numbers, I would like a formula to
pull the three reference numbers on the list and then I would use sumif to
add up the volumes per reference number.

Thanks

Summarised list from long list
 

I think you have nailed it on the head, thanks Mike H

"Mike H" wrote:

If i've understood correctly you want to extract the unique value in your
range B2:B11. Try this in E2 and drag down. It's an array formula so
Ctrl+Shift+enter

=IF(COUNTIF($B$2:B2,B2)=1,B2,"")

It will leave a space so delete the cell and shift cells up.

Mike

"Sunnyskies" wrote:

Morning,

In cells B2 to B14 I have got three seperate reference numbers i.e. 1740,
1741 & 1742. For example in cells B2, B4, B8, B9 & B10 there would be 1740,
then in cells B3, B7 and B11 there would be 1741. 1742 would be in B5, B6,
B12, B13 & B14.
Now in cells C2 to C14 I have got volumes relating the the respective
reference.

I want to create a list (in cell E2 to E5) just listing the three reference
numbers then summing up the volumes associated to the reference number.

I do not want to type in the reference numbers, I would like a formula to
pull the three reference numbers on the list and then I would use sumif to
add up the volumes per reference number.

Thanks

Summarised list from long list
 

summing up the volumes associated to the reference number would be:

+IF(E2="","",SUMPRODUCT(($B$2:$B$14=E2)*($C$2:$C$1 4)))




"Sunnyskies" wrote:

Morning,

In cells B2 to B14 I have got three seperate reference numbers i.e. 1740,
1741 & 1742. For example in cells B2, B4, B8, B9 & B10 there would be 1740,
then in cells B3, B7 and B11 there would be 1741. 1742 would be in B5, B6,
B12, B13 & B14.
Now in cells C2 to C14 I have got volumes relating the the respective
reference.

I want to create a list (in cell E2 to E5) just listing the three reference
numbers then summing up the volumes associated to the reference number.

I do not want to type in the reference numbers, I would like a formula to
pull the three reference numbers on the list and then I would use sumif to
add up the volumes per reference number.

Thanks