Help with summation of different rows in 1 column

bassemjohn1

Board Regular
Joined
Jun 26, 2020
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
Hello guys,

I am new here and am in need of your help,I have a report that is really taking a huge time
and all I need is a way to sum what's in column D (invoice amount) for each supplier and put it in in column F (under Diff)
for example
summation of Hertz invoice amount then Oriental and so on
is there any sum equation to sum the invoice amount numbers related to each supplier without me having to sum it manually
for each supplier knowing that the start and number of rows vary from supplier to another.

1593178923796.png
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hi & welcome to MrExcel.
Maybe
=SUMIFS(D:D,A:A,A2)
 
Upvote 0
that's very helpful, but still 1 problem though
1593180395160.png



it captures the whole column, for example: for Hertz i just need the summation to start from Row A4 and end at A7(column D)
while for Oriental i want it from A11 and end at A13 where my invoice amount is and so on for other supplers
how can i do that with equation ?
 

Attachments

  • 1593180308557.png
    1593180308557.png
    68.2 KB · Views: 28
  • 1593180374395.png
    1593180374395.png
    66.8 KB · Views: 28
Upvote 0
Will the same supplier appear in different areas of the sheet?
 
Upvote 0
In that case you can just put the formula in the first row of each supplier, changing the A2 to reflect the row with the formula.
 
Upvote 0
unfortunately, i have no luck with it :/

amusing that awesome add-in XL2bb for you to help me find a solution
test ask.xls
ABCDEF
1SupCodeSup NameINV DateInvoAmountOPay AmountODiff
20013Hertz Car Rentals7,758.89
3AED7,758.890.00
40013102020 05/02/20201,806.000.001,806.00
50013223434 05/02/20202,457.000.002,457.00
600132132145 05/14/20208.840.008.84
700132132323 04/02/20203,487.050.003,487.05
8SupCodeSup NameINV DateInvoAmountOPay AmountODiff
90073ORIENTAL TRADING CO.9,313.62
10USD9,313.620.00
1100732000865 05/20/20201,295.270.004,757.57
1200732000865 05/20/20203,607.350.0013,249.90
1300732000865 11/24/20194,411.000.0016,201.74
14SupCodeSup NameINV DateInvoAmountOPay AmountODiff
150110Kanoo Group Travel77352.75
16AED77,352.750.00
170110900095 09/27/2019-875.000.00-875.00
180110900095 10/31/2019-913.000.00-913.00
190110900095 09/10/20192,110.000.002,110.00
200110900095 09/15/20191,275.000.001,275.00
210110900095 09/17/20192,745.000.002,745.00
220110900095 09/15/20192,535.000.002,535.00
230110900095 09/30/2019427.500.00427.50
240110900095 12/19/2019330.750.00330.75
250110900095 03/26/2020735.000.00735.00
260110900095 09/14/2019815.000.00815.00
270110900095 09/15/20191,405.000.001,405.00
280110900095 09/25/2019720.000.00720.00
290110900095 09/25/2019455.000.00455.00
300110900095 09/30/20192,050.000.002,050.00
310110900095 09/30/20191,625.000.001,625.00
320110900095 04/20/20202,165.000.002,165.00
330110900095 10/07/20194,925.000.004,925.00
340110900095 10/15/2019367.500.00367.50
350110900095 10/30/2019520.000.00520.00
360110900095 09/25/20191,355.000.001,355.00
370110900095 09/27/20193,585.000.003,585.00
380110900095 09/27/20193,715.000.003,715.00
390110900095 09/27/20192,380.000.002,380.00
400110900095 09/23/20191,340.000.001,340.00
410110900095 04/03/20202,300.000.002,300.00
420110900095 09/27/2019350.000.00350.00
430110900095 03/09/20202,465.000.002,465.00
440110900095 03/09/20202,035.000.002,035.00
450110900095 06/30/20192,130.000.002,130.00
460110900095 06/30/2019700.000.00700.00
470110900095 03/20/20201,665.000.001,665.00
480110900095 09/17/20192,745.000.002,745.00
490110900095 01/07/2020-855.000.00-855.00
500110900095 09/15/20191,895.000.001,895.00
510110900095 09/15/20193,645.000.003,645.00
520110900095 09/23/20196,980.000.006,980.00
530110900095 09/15/20191,340.000.001,340.00
540110900095 04/30/20195,365.000.005,365.00
550110900095 12/12/20198,800.000.008,800.00
5677,352.75
57SupCodeSup NameINV DateInvoAmountOPay AmountODiff
580130BIN SALIM ENTERPRISES L.L.C
59USD2,736.500.00
600130173826 05/17/20202,736.500.0010,051.25
Sheet1
Cell Formulas
RangeFormula
F2,F9F2=SUMIFS(D:D,A:A,A2)
D56D56=SUM(D17:D55)
Named Ranges
NameRefers ToCells
_FilterDatabase=Sheet1!$A$1:$F$60F9, F2


i really appreciate your help.
 
Upvote 0
In what way isn't it working?
The formulae in F2 & F9 seem to be giving the correct answer, unless I've misunderstood something.
 
Upvote 0
In what way isn't it working?
The formulae in F2 & F9 seem to be giving the correct answer, unless I've misunderstood something.
oh well I didn't notice, am so sorry
thanks so much Fluff, you really saved me a lot of time doing it manually for hundreds of suppliers
thanks a lot !!.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

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