Need help building a formula

[cutepal]

Hello,

I have a series of names and values assigned to each of these names. I need to add "all" the values assigned to the same name and post this total in each of the rows.

Please see the example below - I have to add all the values from Line amount and Tax amount for each contact and input the total of these values in all the rows. How can I achieve this? Thank you for your help!

ContactName	InvoiceAmountDue	LineAmount	TaxAmount
Mr White	37.58	0	0
Mr White	37.58	27.36	2.74
Mr White	37.58	0	0
Mr White	37.58	6.8	0.68
Mr Red	1420.25	0	0
Mr Red	1420.25	1163.94	116.39
Mr Red	1420.25	120.4	12.04
Mr Red	1420.25	6.8	0.68
Mr Green	637.45	0	0
Mr Green	637.45	518.7	51.87
Mr Green	637.45	54	5.4
Mr Green	637.45	6.8	0.68
Mr Yellow	178.01	143.83	14.38
Mr Yellow	178.01	11.2	1.12
Mr Yellow	178.01	6.8	0.68
Mr Blue	65.43	0	0
Mr Blue	65.43	47.88	4.79
Mr Blue	65.43	4.8	0.48
Mr Blue	65.43	6.8	0.68
Mr Purple	318.12	250.8	25.08
Mr Purple	318.12	31.6	3.16
Mr Purple	318.12	6.8	0.68

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[jtakw]

Hi,

When you say add "all" values, do you mean including Invoice Amount Due?

 

[cutepal]

No...Invoice Amount Due is where we need to input the total of "Line Amount" and "Tax Due". If you see the example for Mr White, all his values of Line amount and Tax Due are added and the final total is inserted in "Invoice Amount Due"

 

[FDibbins]

It wasn't clear (to me, anyway) if you wanted separate columns for each total, or 1 total for both, so I did it both ways for you

	A​	B​	C​	D​	E​	F​	G​
1​	ContactName	InvoiceAmountDue	LineAmount	TaxAmount	SUMLine	SUMTax	SUMBoth
2​	Mr White	37.58	0​	0​	34.16​	3.42​	37.58​
3​	Mr White	37.58	27.36​	2.74​	34.16​	3.42​	37.58​
4​	Mr White	37.58	0​	0​	34.16​	3.42​	37.58​
5​	Mr White	37.58	6.8​	0.68​	34.16​	3.42​	37.58​
6​	Mr Red	1420.25	0​	0​	1291.14​	129.11​	1420.25​
7​	Mr Red	1420.25	1163.94​	116.39​	1291.14​	129.11​	1420.25​
8​	Mr Red	1420.25	120.4​	12.04​	1291.14​	129.11​	1420.25​
9​	Mr Red	1420.25	6.8​	0.68​	1291.14​	129.11​	1420.25​
10​	Mr Green	637.45	0​	0​	579.5​	57.95​	637.45​
11​	Mr Green	637.45	518.7​	51.87​	579.5​	57.95​	637.45​
12​	Mr Green	637.45	54​	5.4​	579.5​	57.95​	637.45​
13​	Mr Green	637.45	6.8​	0.68​	579.5​	57.95​	637.45​
14​	Mr Yellow	178.01	143.83​	14.38​	161.83​	16.18​	178.01​
15​	Mr Yellow	178.01	11.2​	1.12​	161.83​	16.18​	178.01​
16​	Mr Yellow	178.01	6.8​	0.68​	161.83​	16.18​	178.01​
17​	Mr Blue	65.43	0​	0​	59.48​	5.95​	65.43​
18​	Mr Blue	65.43	47.88​	4.79​	59.48​	5.95​	65.43​
19​	Mr Blue	65.43	4.8​	0.48​	59.48​	5.95​	65.43​
20​	Mr Blue	65.43	6.8​	0.68​	59.48​	5.95​	65.43​
21​	Mr Purple	318.12	250.8​	25.08​	289.2​	28.92​	318.12​
22​	Mr Purple	318.12	31.6​	3.16​	289.2​	28.92​	318.12​
23​	Mr Purple	318.12	6.8​	0.68​	289.2​	28.92​	318.12​

E2=SUMIF($A$2:$A$23,$A2,C$2:C$23)
copied down and across to column F
G2=SUMPRODUCT(--($A$2:$A$23=$A2)*(C$2:D$23))
copied down

 

[FDibbins]

Based on post #3, you need to use the SP formula

 

[cutepal]

Oh my god! You are a genius!!! Thank you SO MUCH for your quick response. You have no idea how helpful this formula is to me. I will be saving 2 days of time with this formula. Thank you again!

 

[ttray33y]

Hello,

I have a series of names and values assigned to each of these names. I need to add "all" the values assigned to the same name and post this total in each of the rows.

Please see the example below - I have to add all the values from Line amount and Tax amount for each contact and input the total of these values in all the rows. How can I achieve this? Thank you for your help!

ContactName	InvoiceAmountDue	LineAmount	TaxAmount
Mr White	37.58	0	0
Mr White	37.58	27.36	2.74
Mr White	37.58	0	0
Mr White	37.58	6.8	0.68
Mr Red	1420.25	0	0
Mr Red	1420.25	1163.94	116.39
Mr Red	1420.25	120.4	12.04
Mr Red	1420.25	6.8	0.68
Mr Green	637.45	0	0
Mr Green	637.45	518.7	51.87
Mr Green	637.45	54	5.4
Mr Green	637.45	6.8	0.68
Mr Yellow	178.01	143.83	14.38
Mr Yellow	178.01	11.2	1.12
Mr Yellow	178.01	6.8	0.68
Mr Blue	65.43	0	0
Mr Blue	65.43	47.88	4.79
Mr Blue	65.43	4.8	0.48
Mr Blue	65.43	6.8	0.68
Mr Purple	318.12	250.8	25.08
Mr Purple	318.12	31.6	3.16
Mr Purple	318.12	6.8	0.68

<tbody>
</tbody>

theres a function called Subtotal.
highlight your data go to Data [TAB] > Outline > Subtotal.
in the Subtotal Dialog [check the ff] > LineAmount & TaxAmount > Use function: Sum > Click OK

 

[cutepal]

Sir, if I may ask one more question.... Say Column C & D are not next to each other, then I won't be able to use the sum formula of (c$2:D$23), right? If Line amount is in Column C and Tax Amount is in Column Y, how do I combine the values from these two columns?

 

[cutepal]

I think I figured it out - thank you, once again.

=SUMPRODUCT(--($A$2:$A$23=$A2)*(C$2:C$23+Y$2:Y$23))

 

[FDibbins]

(there is probably a complex way to do this with 1 formula, but this is simpler) Use my 1st suggestion then...
E2=SUMIF($A$2:$A$23,$A2,C$2:C$23)

You would then need to use that twice and add them together
=SUMIF($A$2:$A$23,$A2,C$2:C$23)+SUMIF($A$2:$A$23,$A2,Y$2:Y$23)
If needed, you could use full-column references...
=SUMIF($A:$A,$A2,C:C)+SUMIF($A:$A,$A2,Y:Y)