SUM Condition

[Adam Bradley]

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?

 

[JoeU2004]

I need a formula to get the totals of each reference (0535, 4966).

=sumproduct((A1:A7="0535")*(D17))

=sumproduct((A1:A7)="4966")*(D17))

Those say: "sum the values in D17 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]

Thanks, I ended up using SUMIF...

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

Although I prefer your syntax : )

 

[Pete_UK]

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

 

[Shane Devenshire]

Hi,

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