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

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

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

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