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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Cust code[/TD]
[TD]Inv No[/TD]
[TD]Date[/TD]
[TD]Currency[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]1[/TD]
[TD]02/05/2017[/TD]
[TD]USD[/TD]
[TD]1200.00[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]2[/TD]
[TD]08/05/2017[/TD]
[TD]GBP[/TD]
[TD]125.00[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]8[/TD]
[TD]12/07/2017[/TD]
[TD]GBP[/TD]
[TD]250.20[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]12[/TD]
[TD]18/07/2017[/TD]
[TD]USD[/TD]
[TD]100.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Cust code[/TD]
[TD]Inv No[/TD]
[TD]Date[/TD]
[TD]Currency[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]4[/TD]
[TD]02/05/2017[/TD]
[TD]USD[/TD]
[TD]1200.00[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]6[/TD]
[TD]08/05/2017[/TD]
[TD]GBP[/TD]
[TD]125.00[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]14[/TD]
[TD]12/07/2017[/TD]
[TD]GBP[/TD]
[TD]250.20[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.

[TABLE="width: 814"]
<colgroup><col span="2"><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Cust code[/TD]
[TD]Inv No[/TD]
[TD]Date[/TD]
[TD]Currency[/TD]
[TD]Amount[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]1[/TD]
[TD]42857[/TD]
[TD]USD[/TD]
[TD]1200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]2[/TD]
[TD]42863[/TD]
[TD]GBP[/TD]
[TD]125[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]8[/TD]
[TD]42928[/TD]
[TD]GBP[/TD]
[TD]250.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]12[/TD]
[TD]42934[/TD]
[TD]USD[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]GBP[/TD]
[TD]375.2[/TD]
[TD]=SUMIF(D1:D5,D7,E1:E5)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]USD[/TD]
[TD]1300[/TD]
[TD]=SUMIF(D1:D5,D8,E1:E5)[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Cust code[/TD]
[TD]Inv No[/TD]
[TD]Date[/TD]
[TD]Currency[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

100​

[/TD]
[TD]

1​

[/TD]
[TD]

5/2/2017​

[/TD]
[TD]USD[/TD]
[TD]

1200​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]USD[/TD]
[TD]GBP[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

100​

[/TD]
[TD]

2​

[/TD]
[TD]

5/8/2017​

[/TD]
[TD]GBP[/TD]
[TD]

125​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

100​

[/TD]
[TD]

1300​

[/TD]
[TD]

375.2​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

100​

[/TD]
[TD]

8​

[/TD]
[TD]

7/12/2017​

[/TD]
[TD]GBP[/TD]
[TD]

250.2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

200​

[/TD]
[TD]

1200​

[/TD]
[TD]

375.2​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

100​

[/TD]
[TD]

12​

[/TD]
[TD]

7/18/2017​

[/TD]
[TD]USD[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Cust code[/TD]
[TD]Inv No[/TD]
[TD]Date[/TD]
[TD]Currency[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

200​

[/TD]
[TD]

4​

[/TD]
[TD]

5/2/2017​

[/TD]
[TD]USD[/TD]
[TD]

1200​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

200​

[/TD]
[TD]

6​

[/TD]
[TD]

5/8/2017​

[/TD]
[TD]GBP[/TD]
[TD]

125​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

200​

[/TD]
[TD]

14​

[/TD]
[TD]

7/12/2017​

[/TD]
[TD]GBP[/TD]
[TD]

250.2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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