Sumif formula with dynamic range

[rbnaik]

Hello everyone,
I am trying to create a common sumif formula for different ranges in different excel files. I extract complete list for all customers and then create a separate workbook for each of these customers. Hence these files relate to different customers, with whom I may be dealing in different currencies for which number of outstanding invoices (rows) may differ. I need to insert sumif formula after leaving one blank row in each file. I do not want to freeze number of rows (1 to 10) or columns (a to h)
Following are the two examples.

Cust code	Inv No	Date	Currency	Amount
100	1	02/05/2017	USD	1200.00
100	2	08/05/2017	GBP	125.00
100	8	12/07/2017	GBP	250.20
100	12	18/07/2017	USD	100.00

<tbody>
</tbody>

Cust code	Inv No	Date	Currency	Amount
200	4	02/05/2017	USD	1200.00
200	6	08/05/2017	GBP	125.00
200	14	12/07/2017	GBP	250.20

<tbody>
</tbody>

 

[etaf]

Just one formula ?
you have 2 currencies USB and GBP
how many other currencies do you have

sumif( D:D, "USD", E:E)
sumif( D:D, "GBP", E:E)

D = Column Currency
E = Column Amount

Assuming that each workbook only has 1 customer in

but , i'm not really sure exactly what you are after

 

[rbnaik]

Hi etaf,
I think I was not clear. One customer file may contain multiple currencies, so I need sumif for each currency. I have managed upto getting different currencies, but I am not able to insert the sumif formula as the rows as well as columns will keep changing. if I can have one sumif formula right, I will do it for other currencies also.

Cust code	Inv No	Date	Currency	Amount
100	1	42857	USD	1200
100	2	42863	GBP	125
100	8	42928	GBP	250.2
100	12	42934	USD	100
			GBP	375.2	=SUMIF(D1:D5,D7,E1:E5)
			USD	1300	=SUMIF(D1:D5,D8,E1:E5)

<colgroup><col span="2"><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Cust code	Inv No	Date	Currency	Amount
2​	100​	1​	5/2/2017​	USD	1200​				USD	GBP
3​	100​	2​	5/8/2017​	GBP	125​			100​	1300​	375.2​
4​	100​	8​	7/12/2017​	GBP	250.2​			200​	1200​	375.2​
5​	100​	12​	7/18/2017​	USD	100​
6​
7​
8​
9​
10​	Cust code	Inv No	Date	Currency	Amount
11​	200​	4​	5/2/2017​	USD	1200​
12​	200​	6​	5/8/2017​	GBP	125​
13​	200​	14​	7/12/2017​	GBP	250.2​

<tbody>
</tbody>

I3=SUMIFS($E$2:$E$13,$A$2:$A$13,$H3,$D$2:$D$13,I$2) copy across and down

 

[rbnaik]

Hi Marzitullio,
My requirement is to change defined range to dynamic range as follows:

e.g.

Original code -> 'Range("I1:I108").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("I1:I108"), CopyToRange:=Range("I111"), Unique:=True




Is replaced by -> Range(Cells(1, ActiveCell.Column), Cells(ActiveCell.Row, ActiveCell.Column)).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range(Cells(1, ActiveCell.Column), Cells(ActiveCell.Row, ActiveCell.Column)), CopyToRange:=Range(lastcell), Unique:=True

Same way I need to replace the following code as the number of rows will keep changing... (in the following example the rows are 110) and column may change:
ActiveCell.FormulaR1C1 = "=SUMIF(R[Datarows*-1]C[1]:R[-3]C[1],RC[1],R[-110]C:R[-3]C)"

 

[rbnaik]

Any help ?

 

[BGY23]

Hi,

I would imagine that by far the easiset way to do what you're asking is to use a pivot table on the master file and copy the seasualts to the new workbook(s). They are very versatile in data mining and summary by specific criteria.

 

[BGY23]

# seasualts ????? results