Help with summation of different rows in 1 column

[bassemjohn1]

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.

 

[bassemjohn1]

If you can do not keep any space between the vendors, just make a database sheetwith the following columns

SupCode

currency

Sup Name

INV No.

INV Date

InvoAmountO

Pay AmountO

Diff

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

that's awesome way to do it !! but sadly I have to stick to 1 sheet according to their policy.

 

[bassemjohn1]

In that case try

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

I wanna thank you for the time you saved me this month

 

[bassemjohn1]

i have 1 last obstacle

is there any way I can add the bank account automatically

For Ex: for TASC >> I use AED-001 account
and for oracle >> I use USD -001

I add them manually each month in column H , so is there any way I can let excel detect that it's oracle supplier for example and automatically add USD-001 to column H.

AEJ P.P.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	SupCode	Sup Name	INV Date	Tran No	InvoAmountO	Pay AmountO	Diff	DIV	PGC	BEDATE	CUR	DUEDATE
2	0019	TASC Labour services LLC					13,298.25	AED - 001
3	AED		0.00
4	0019		28-Jun-20		13,298.25	0.00
5	SupCode	Sup Name	INV Date	Tran No	InvoAmountO	Pay AmountO	Diff	DIV	PGC	BEDATE	CUR	DUEDATE
6	0052	Oracle Netsuite					2,267.46	USD - 102
7	USD		0.00
8	0052		18-Feb-20		2,267.46	0.00
9	SupCode	Sup Name	INV Date	Tran No	InvoAmountO	Pay AmountO	Diff	DIV	PGC	BEDATE	CUR	DUEDATE
10	0272	On Time Shipping Services LLC					6,664.00	AED - 002
11	AED		0.00
12	0272		4-Mar-20		1,529.00	0.00
13	0272		2-Mar-20		3,750.00	0.00
14	0272		8-Jul-20		1,385.00	0.00
Sheet1

Cell Formulas
Range	Formula
G2,G10,G6	G2	=SUMIFS(E:E,A:A,A2)-SUMIFS(F:F,A:A,A2)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$L$52	G10, G6, G2

 

[Fluff]

As this is now a totally different question, please start a new thread.
Thanks