Formula to replace a Pivot table function

[Guest]

Morning,

I have got three reference numbers in cells B2 to B12, 1740, 1741 & 1742. So
in cells B2, B4, B7, B8 there is 1740. In cells B3, B9, B10 & B12 is 1741,
then in cells B5, B6 & B11 is 1742.

In cells C2 to C12 is volumes per reference number.

I want a formula to put the three reference numbers in cells E2 to E4 and
then I would sumif the volumes to its respective reference number.

I am looking for a formula to work like a pivot table, but need to keep the
file size small, thus looking for a formula instead.

Thanks

 

[Guest]

In E2 thru E4 enter:

=SUMPRODUCT(--(B2:B12=1740),(C2:C12))
=SUMPRODUCT(--(B2:B12=1741),(C2:C12))
=SUMPRODUCT(--(B2:B12=1742),(C2:C12))

Here is some sample stuff:

1740 210 2204
1741 327 4731
1740 408 1165
1742 535
1742 630
1740 714
1740 872
1741 945
1741 1030
1741 1133
1741 1296

The advantage to using formulas is that values automatically adjust without
having to re-run the Pivot.

 

[Guest]

Morning Gary,

Thats looks like it can work, except you have physically typed the reference
numbers in the formula.

This document will be going out the field and each depot will have unique
numbering.

Will it be possible to have the reference "inserted" into the formual
automatically?

 

[Guest]

It is possible, with two extra "helper" columns

 

[Guest]

Morning Gary,

Please explain further.

It is possible, with two extra "helper" columns