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
 
In that case you don't have any cells in col F on the active sheet with the word "Diff"
Did you have the correct sheet visible when you ran the code?
yes correct I changed the cell in the main sheet, I'll revert to it and try again now
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
it's working like charm now ! again thanks for help :D
and if I want to apply the same macro across different sheet, what shall I do ?
 
Upvote 0
The code will work on which ever sheet is active.
 
Upvote 0
i know am asking alot but that's my last 2 questions:
1) can I add the code you made (bassemjohn) to Macro 5 instead of having 2 macros
can I make it only 1 macro.

1593371734657.png


2)
would you please make a code for this equation
=SUMIFS(E:E,A:A,A2)-SUMIFS(F:F,A:A,A2)
 
Upvote 0
1) yes you can add my code to yours :)
2) where do you want that formula to go?
 
Upvote 0
1) yes you can add my code to yours :)
2) where do you want that formula to go?
1) when I add it, always a debugging error occurs
2) to the macro, it's an update for the formula u tailored for me first
 
Upvote 0
Does the formula still need to go in column F? If so it will create a circular reference & so wont work.

Can you post the code for your other macro
 
Upvote 0
If you can do not keep any space between the vendors, just make a database sheetwith the following columns

SupCodecurrencySup NameINV No.INV DateInvoAmountOPay AmountODiff

the difference column, cells should have inv amt-paid amt.

Now at the end of this table say in column Z, make a list of supplier name ( If needed make the column Sup name to be data validated for list from this table ) so say in cell Z2 you have hertz and cell Z3 you have the entry oriental trading co, for the column containing supplier name you will be able to select these names, increase selection as much as you want .

In Column AA 2 put this formula =SUMIF(F:F,Z2,G:G) In this change F to the column name of your supplier name, and change G to the column name of your difference THEN YOU WILL GET THE TOTAL DIFFERENCE AMOUNT FOR EACH VENDOR
Not only that in Column AB2 put this formula =SUMIF(F:F,Z2,G:G) In this change F to the column name of your supplier name, and change G to the column name of your Inv. amt, THEN YOU WILL GET THE TOTAL INVOICE AMOUNT FOR EACH VENDOR
 
Upvote 0
Does the formula still need to go in column F? If so it will create a circular reference & so wont work.

Can you post the code for your other macro
No it will go in that column G2, that's the final image and the quation will be =SUMIFS(E:E,A:A,A2)-SUMIFS(F:F,A:A,A2)
1593439746190.png
 
Upvote 0
In that case try
VBA Code:
Sub bassemjohn()
   With Range("G1", Range("G" & Rows.Count).End(xlUp))
      .Replace "Diff", True, xlWhole, , False, , False, False
      .SpecialCells(xlConstants, xlLogical).Offset(1).FormulaR1C1 = "=sumifs(c5,c1,rc1)-sumifs(c6,c1,rc1)"
      .Replace True, "Diff", xlWhole, , False, , False, False
   End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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