Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM Condition
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM Condition
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM Condition
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM Condition
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if with or condition | Excel Worksheet Functions | |||
MAX with condition | Excel Discussion (Misc queries) | |||
lookup with multiple condition, but one condition to satisfy is en | Excel Worksheet Functions | |||
Combine an OR condition with an AND condition | Excel Discussion (Misc queries) | |||
Condition 1 overules condition 2? | Excel Worksheet Functions |