Help with summation of different rows in 1 column

bassemjohn1

New Member
Joined
Jun 26, 2020
Messages
16
Office Version
2016
Platform
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
Maybe
=SUMIFS(D:D,A:A,A2)
 

bassemjohn1

New Member
Joined
Jun 26, 2020
Messages
16
Office Version
2016
Platform
Windows
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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
Will the same supplier appear in different areas of the sheet?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
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.
 

bassemjohn1

New Member
Joined
Jun 26, 2020
Messages
16
Office Version
2016
Platform
Windows
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 Rentals
7,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
56
77,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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
In what way isn't it working?
The formulae in F2 & F9 seem to be giving the correct answer, unless I've misunderstood something.
 

bassemjohn1

New Member
Joined
Jun 26, 2020
Messages
16
Office Version
2016
Platform
Windows
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 !!.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,037
Messages
5,472,112
Members
406,804
Latest member
xbinsx

This Week's Hot Topics

Top