# Help with summation of different rows in 1 column

#### bassemjohn1

##### New Member
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.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Maybe
=SUMIFS(D:D,A:A,A2)

#### bassemjohn1

##### New Member
that's very helpful, but still 1 problem though

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

• 68.2 KB Views: 27
• 66.8 KB Views: 27

#### Fluff

##### MrExcel MVP, Moderator
Will the same supplier appear in different areas of the sheet?

#### bassemjohn1

##### New Member
Will the same supplier appear in different areas of the sheet?
No

#### Fluff

##### MrExcel MVP, Moderator
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
unfortunately, i have no luck with it :/

amusing that awesome add-in XL2bb for you to help me find a solution
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
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

#### Fluff

##### MrExcel MVP, Moderator
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
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
You're welcome & thanks for the feedback.

1,102,241
Messages
5,485,588
Members
407,504
Latest member
inexperiencedOne

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...