I have collumn A as referece numbers such as 0535 which relate to several
values in collumn D eg.
0535 £235
0535 £82
0535 £9523
4966 £12
0535 £534
4966 £211
0535 £23

I need a formula to get the totals of each reference (0535, 4966). Can
anyone make a suggestion please?

"Adam Bradley" <Adam wrote:
I need a formula to get the totals of each reference (0535, 4966).

=sumproduct((A1:A7="0535")*(D1:D7))

=sumproduct((A1:A7)="4966")*(D1:D7))

Those say: "sum the values in D1:D7 that match the value in A1:A7".

That assume that A1:A7 contains text. If it contains number, remove the
quotes from 0535 and 4966 above.


----- original message -----

"Adam Bradley" <Adam wrote in message
...
I have collumn A as referece numbers such as 0535 which relate to several
values in collumn D eg.
0535 £235
0535 £82
0535 £9523
4966 £12
0535 £534
4966 £211
0535 £23

I need a formula to get the totals of each reference (0535, 4966). Can
anyone make a suggestion please?

Thanks, I ended up using SUMIF...

=SUMIF(Data!C:C,4966,Data!E:E) etc

Although I prefer your syntax : )

"JoeU2004" wrote:

"Adam Bradley" <Adam wrote:
I need a formula to get the totals of each reference (0535, 4966).

=sumproduct((A1:A7="0535")*(D1:D7))

=sumproduct((A1:A7)="4966")*(D1:D7))

Those say: "sum the values in D1:D7 that match the value in A1:A7".

That assume that A1:A7 contains text. If it contains number, remove the
quotes from 0535 and 4966 above.


----- original message -----

"Adam Bradley" <Adam wrote in message
...
I have collumn A as referece numbers such as 0535 which relate to several
values in collumn D eg.
0535 £235
0535 £82
0535 £9523
4966 £12
0535 £534
4966 £211
0535 £23

I need a formula to get the totals of each reference (0535, 4966). Can
anyone make a suggestion please?

Try this:

=SUMIF(A:A,"0535",B:B)

A better approach would be to list all the individual reference
numbers, say in column D starting with D1, then you could put this in
E1:

=SUMIF(A:A,D1,B:B)

Then you can copy this down for as many entries as you have in column
D. Ensure that the entries in D are of the same format as those in
column A (i.e. text in your example).

Hope this helps.

Pete

On Jun 8, 3:07*pm, Adam Bradley <Adam
wrote:
I have collumn A as referece numbers such as 0535 which relate to several
values in collumn D *eg.
0535 * * * * *£235
0535 * * * * *£82
0535 * * * * *£9523
4966 * * * * *£12
0535 * * * * *£534
4966 * * * * *£211
0535 * * * * *£23

I need a formula to get the totals of each reference (0535, 4966). *Can
anyone make a suggestion please?

Hi,

In general I prefer SUMIF, when it can be used, over SUMPRODUCT because it
is faster, and a little less obtuse.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Adam Bradley" wrote:

Thanks, I ended up using SUMIF...

=SUMIF(Data!C:C,4966,Data!E:E) etc

Although I prefer your syntax : )

"JoeU2004" wrote:

"Adam Bradley" <Adam wrote:
I need a formula to get the totals of each reference (0535, 4966).

=sumproduct((A1:A7="0535")*(D1:D7))

=sumproduct((A1:A7)="4966")*(D1:D7))

Those say: "sum the values in D1:D7 that match the value in A1:A7".

That assume that A1:A7 contains text. If it contains number, remove the
quotes from 0535 and 4966 above.


----- original message -----

"Adam Bradley" <Adam wrote in message
...
I have collumn A as referece numbers such as 0535 which relate to several
values in collumn D eg.
0535 £235
0535 £82
0535 £9523
4966 £12
0535 £534
4966 £211
0535 £23

I need a formula to get the totals of each reference (0535, 4966). Can
anyone make a suggestion please?