Need help building a formula

C

cutepal

New Member
Joined
Feb 11, 2016
Messages
6
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!
[TABLE="class: grid, width: 428"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]ContactName[/TD]
[TD]InvoiceAmountDue[/TD]
[TD]LineAmount[/TD]
[TD]TaxAmount[/TD]
[/TR]
[TR]
[TD]Mr White[/TD]
[TD]37.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mr White[/TD]
[TD]37.58[/TD]
[TD="align: right"]27.36[/TD]
[TD="align: right"]2.74[/TD]
[/TR]
[TR]
[TD]Mr White[/TD]
[TD]37.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mr White[/TD]
[TD]37.58[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]0.68[/TD]
[/TR]
[TR]
[TD]Mr Red[/TD]
[TD]1420.25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mr Red[/TD]
[TD]1420.25[/TD]
[TD="align: right"]1163.94[/TD]
[TD="align: right"]116.39[/TD]
[/TR]
[TR]
[TD]Mr Red[/TD]
[TD]1420.25[/TD]
[TD="align: right"]120.4[/TD]
[TD="align: right"]12.04[/TD]
[/TR]
[TR]
[TD]Mr Red[/TD]
[TD]1420.25[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]0.68[/TD]
[/TR]
[TR]
[TD]Mr Green[/TD]
[TD]637.45[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mr Green[/TD]
[TD]637.45[/TD]
[TD="align: right"]518.7[/TD]
[TD="align: right"]51.87[/TD]
[/TR]
[TR]
[TD]Mr Green[/TD]
[TD]637.45[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]5.4[/TD]
[/TR]
[TR]
[TD]Mr Green[/TD]
[TD]637.45[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]0.68[/TD]
[/TR]
[TR]
[TD]Mr Yellow[/TD]
[TD]178.01[/TD]
[TD="align: right"]143.83[/TD]
[TD="align: right"]14.38[/TD]
[/TR]
[TR]
[TD]Mr Yellow[/TD]
[TD]178.01[/TD]
[TD="align: right"]11.2[/TD]
[TD="align: right"]1.12[/TD]
[/TR]
[TR]
[TD]Mr Yellow[/TD]
[TD]178.01[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]0.68[/TD]
[/TR]
[TR]
[TD]Mr Blue[/TD]
[TD]65.43[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mr Blue[/TD]
[TD]65.43[/TD]
[TD="align: right"]47.88[/TD]
[TD="align: right"]4.79[/TD]
[/TR]
[TR]
[TD]Mr Blue[/TD]
[TD]65.43[/TD]
[TD="align: right"]4.8[/TD]
[TD="align: right"]0.48[/TD]
[/TR]
[TR]
[TD]Mr Blue[/TD]
[TD]65.43[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]0.68[/TD]
[/TR]
[TR]
[TD]Mr Purple[/TD]
[TD]318.12[/TD]
[TD="align: right"]250.8[/TD]
[TD="align: right"]25.08[/TD]
[/TR]
[TR]
[TD]Mr Purple[/TD]
[TD]318.12[/TD]
[TD="align: right"]31.6[/TD]
[TD="align: right"]3.16[/TD]
[/TR]
[TR]
[TD]Mr Purple[/TD]
[TD]318.12[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]0.68[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Hi,

When you say add "all" values, do you mean including Invoice Amount Due?
 
Last edited:
Upvote 0
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"
 
Upvote 0
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
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][/tr]
[tr][td]
1​
[/td][td]ContactName[/td][td]InvoiceAmountDue[/td][td]LineAmount[/td][td]TaxAmount[/td][td]SUMLine[/td][td]SUMTax[/td][td]SUMBoth[/td][/tr]

[tr][td]
2​
[/td][td]Mr White[/td][td]37.58[/td][td]
0​
[/td][td]
0​
[/td][td]
34.16​
[/td][td]
3.42​
[/td][td]
37.58​
[/td][/tr]

[tr][td]
3​
[/td][td]Mr White[/td][td]37.58[/td][td]
27.36​
[/td][td]
2.74​
[/td][td]
34.16​
[/td][td]
3.42​
[/td][td]
37.58​
[/td][/tr]

[tr][td]
4​
[/td][td]Mr White[/td][td]37.58[/td][td]
0​
[/td][td]
0​
[/td][td]
34.16​
[/td][td]
3.42​
[/td][td]
37.58​
[/td][/tr]

[tr][td]
5​
[/td][td]Mr White[/td][td]37.58[/td][td]
6.8​
[/td][td]
0.68​
[/td][td]
34.16​
[/td][td]
3.42​
[/td][td]
37.58​
[/td][/tr]

[tr][td]
6​
[/td][td]Mr Red[/td][td]1420.25[/td][td]
0​
[/td][td]
0​
[/td][td]
1291.14​
[/td][td]
129.11​
[/td][td]
1420.25​
[/td][/tr]

[tr][td]
7​
[/td][td]Mr Red[/td][td]1420.25[/td][td]
1163.94​
[/td][td]
116.39​
[/td][td]
1291.14​
[/td][td]
129.11​
[/td][td]
1420.25​
[/td][/tr]

[tr][td]
8​
[/td][td]Mr Red[/td][td]1420.25[/td][td]
120.4​
[/td][td]
12.04​
[/td][td]
1291.14​
[/td][td]
129.11​
[/td][td]
1420.25​
[/td][/tr]

[tr][td]
9​
[/td][td]Mr Red[/td][td]1420.25[/td][td]
6.8​
[/td][td]
0.68​
[/td][td]
1291.14​
[/td][td]
129.11​
[/td][td]
1420.25​
[/td][/tr]

[tr][td]
10​
[/td][td]Mr Green[/td][td]637.45[/td][td]
0​
[/td][td]
0​
[/td][td]
579.5​
[/td][td]
57.95​
[/td][td]
637.45​
[/td][/tr]

[tr][td]
11​
[/td][td]Mr Green[/td][td]637.45[/td][td]
518.7​
[/td][td]
51.87​
[/td][td]
579.5​
[/td][td]
57.95​
[/td][td]
637.45​
[/td][/tr]

[tr][td]
12​
[/td][td]Mr Green[/td][td]637.45[/td][td]
54​
[/td][td]
5.4​
[/td][td]
579.5​
[/td][td]
57.95​
[/td][td]
637.45​
[/td][/tr]

[tr][td]
13​
[/td][td]Mr Green[/td][td]637.45[/td][td]
6.8​
[/td][td]
0.68​
[/td][td]
579.5​
[/td][td]
57.95​
[/td][td]
637.45​
[/td][/tr]

[tr][td]
14​
[/td][td]Mr Yellow[/td][td]178.01[/td][td]
143.83​
[/td][td]
14.38​
[/td][td]
161.83​
[/td][td]
16.18​
[/td][td]
178.01​
[/td][/tr]

[tr][td]
15​
[/td][td]Mr Yellow[/td][td]178.01[/td][td]
11.2​
[/td][td]
1.12​
[/td][td]
161.83​
[/td][td]
16.18​
[/td][td]
178.01​
[/td][/tr]

[tr][td]
16​
[/td][td]Mr Yellow[/td][td]178.01[/td][td]
6.8​
[/td][td]
0.68​
[/td][td]
161.83​
[/td][td]
16.18​
[/td][td]
178.01​
[/td][/tr]

[tr][td]
17​
[/td][td]Mr Blue[/td][td]65.43[/td][td]
0​
[/td][td]
0​
[/td][td]
59.48​
[/td][td]
5.95​
[/td][td]
65.43​
[/td][/tr]

[tr][td]
18​
[/td][td]Mr Blue[/td][td]65.43[/td][td]
47.88​
[/td][td]
4.79​
[/td][td]
59.48​
[/td][td]
5.95​
[/td][td]
65.43​
[/td][/tr]

[tr][td]
19​
[/td][td]Mr Blue[/td][td]65.43[/td][td]
4.8​
[/td][td]
0.48​
[/td][td]
59.48​
[/td][td]
5.95​
[/td][td]
65.43​
[/td][/tr]

[tr][td]
20​
[/td][td]Mr Blue[/td][td]65.43[/td][td]
6.8​
[/td][td]
0.68​
[/td][td]
59.48​
[/td][td]
5.95​
[/td][td]
65.43​
[/td][/tr]

[tr][td]
21​
[/td][td]Mr Purple[/td][td]318.12[/td][td]
250.8​
[/td][td]
25.08​
[/td][td]
289.2​
[/td][td]
28.92​
[/td][td]
318.12​
[/td][/tr]

[tr][td]
22​
[/td][td]Mr Purple[/td][td]318.12[/td][td]
31.6​
[/td][td]
3.16​
[/td][td]
289.2​
[/td][td]
28.92​
[/td][td]
318.12​
[/td][/tr]

[tr][td]
23​
[/td][td]Mr Purple[/td][td]318.12[/td][td]
6.8​
[/td][td]
0.68​
[/td][td]
289.2​
[/td][td]
28.92​
[/td][td]
318.12​
[/td][/tr]
[/table]
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
 
Upvote 0
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!
 
Upvote 0
cutepal said:
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!
[TABLE="class: grid, width: 428"]
<tbody>[TR]
[TD]ContactName[/TD]
[TD]InvoiceAmountDue[/TD]
[TD]LineAmount[/TD]
[TD]TaxAmount[/TD]
[/TR]
[TR]
[TD]Mr White[/TD]
[TD]37.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mr White[/TD]
[TD]37.58[/TD]
[TD="align: right"]27.36[/TD]
[TD="align: right"]2.74[/TD]
[/TR]
[TR]
[TD]Mr White[/TD]
[TD]37.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mr White[/TD]
[TD]37.58[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]0.68[/TD]
[/TR]
[TR]
[TD]Mr Red[/TD]
[TD]1420.25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mr Red[/TD]
[TD]1420.25[/TD]
[TD="align: right"]1163.94[/TD]
[TD="align: right"]116.39[/TD]
[/TR]
[TR]
[TD]Mr Red[/TD]
[TD]1420.25[/TD]
[TD="align: right"]120.4[/TD]
[TD="align: right"]12.04[/TD]
[/TR]
[TR]
[TD]Mr Red[/TD]
[TD]1420.25[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]0.68[/TD]
[/TR]
[TR]
[TD]Mr Green[/TD]
[TD]637.45[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mr Green[/TD]
[TD]637.45[/TD]
[TD="align: right"]518.7[/TD]
[TD="align: right"]51.87[/TD]
[/TR]
[TR]
[TD]Mr Green[/TD]
[TD]637.45[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]5.4[/TD]
[/TR]
[TR]
[TD]Mr Green[/TD]
[TD]637.45[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]0.68[/TD]
[/TR]
[TR]
[TD]Mr Yellow[/TD]
[TD]178.01[/TD]
[TD="align: right"]143.83[/TD]
[TD="align: right"]14.38[/TD]
[/TR]
[TR]
[TD]Mr Yellow[/TD]
[TD]178.01[/TD]
[TD="align: right"]11.2[/TD]
[TD="align: right"]1.12[/TD]
[/TR]
[TR]
[TD]Mr Yellow[/TD]
[TD]178.01[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]0.68[/TD]
[/TR]
[TR]
[TD]Mr Blue[/TD]
[TD]65.43[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mr Blue[/TD]
[TD]65.43[/TD]
[TD="align: right"]47.88[/TD]
[TD="align: right"]4.79[/TD]
[/TR]
[TR]
[TD]Mr Blue[/TD]
[TD]65.43[/TD]
[TD="align: right"]4.8[/TD]
[TD="align: right"]0.48[/TD]
[/TR]
[TR]
[TD]Mr Blue[/TD]
[TD]65.43[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]0.68[/TD]
[/TR]
[TR]
[TD]Mr Purple[/TD]
[TD]318.12[/TD]
[TD="align: right"]250.8[/TD]
[TD="align: right"]25.08[/TD]
[/TR]
[TR]
[TD]Mr Purple[/TD]
[TD]318.12[/TD]
[TD="align: right"]31.6[/TD]
[TD="align: right"]3.16[/TD]
[/TR]
[TR]
[TD]Mr Purple[/TD]
[TD]318.12[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]0.68[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
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
 
Upvote 0
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?
 
Upvote 0
I think I figured it out - thank you, once again.

=SUMPRODUCT(--($A$2:$A$23=$A2)*(C$2:C$23+Y$2:Y$23))
 
Upvote 0
(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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Help Needed to Match Text in Multiple Cells on Two Sheets and if Match Paste in New Sheet
Replies
1
Views
395
Automatrix
A
Ironman
  • Question Question
Two complex formulas returning incorrect values
2
Replies
11
Views
3K
Ironman
Ironman
C
  • Question Question
Need an formula to track the number of draws a value follows another value.
Replies
1
Views
539
CARBOB
C
C
  • Question Question
Help needed in formula
Replies
3
Views
367
cammy99
C
K
  • Question Question
Eliminating Duplicates and Correlating them to another Value
Replies
0
Views
347
KDR11
K

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top