Help with summation of different rows in 1 column

bassemjohn1

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

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

bassemjohn1

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
The code will work on which ever sheet is active.
 

bassemjohn1

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
1) yes you can add my code to yours :)
2) where do you want that formula to go?
 

bassemjohn1

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

Fluff

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

apgmin

Board Regular
Joined
Mar 1, 2010
Messages
92
Office Version
2013
Platform
Windows
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
 

bassemjohn1

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

Fluff

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

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top