formula for extremely large data divided in two sheets

[raghusri]

I have data of around 2 million records (rows) divided in two sheets (tabs).A sample file attached. How can I apply formula on third sheet that will treat both sheet as continuous sheet.
in the third sheet I want to calculate following things
a)sum of all matirutiy value (value in column "G") for value in column "F" is in >=7<=14(i.e value in g will get added if value in column f is 7 or 8 or 9....14)
b)sum of all matirutiy value (value in column "G") for value in column "F" is in >=15<=30
c)sum of all matirutiy value (value in column "G") for value in column "F" is in >=31<=45
d)sum of all matirutiy value (value in column "G") for value in column "F" is in >=46<=90
d)sum of all matirutiy value (value in column "G") for value in column "F" is in >=91<=120
e)sum of all matirutiy value (value in column "G") for value in column "F" is in >=121<=150
f)sum of all matirutiy value (value in column "G") for value in column "F" is in >=151<=179
g)sum of all matirutiy value (value in column "G") for value in column "F" is in >=180<=269
h)sum of all matirutiy value (value in column "G") for value in column "F" is in >=270<=364
i)sum of all matirutiy value (value in column "G") for value in column "F" is in >=365<=730
j)sum of all matirutiy value (value in column "G") for value in column "F" is in >=731<=1094
k)sum of all matirutiy value (value in column "G") if value in column "F" is in >=1095<=1825
l)sum of all matirutiy value (value in column "G") if value in column "F" is in >=1826<=3650

further i need to make a table
i) sum of all value in column "G" when value in f is >=365<=730 and value in column "E" <=31-12-2019
ii)sum of all value in column "G" when value in f is >=365<=730 and value in column "E" <=31-03-2019
ans so on

in another sheet I want to extract details of those contracts where maturity value is <=10000000.The format will remain same but it will only contain details of records where value in column "G" is <=10000000.

Then in another sheet i have to calculate a) ,b), c)...... and i) ,ii) for data in sheet where value of g is <=10000000.

the formula needs to support dynamic range
Every time the number of records will increase or decrease so when i will paste the data into both sheets the range in formula should adjust them self.
customization should be easy.

Your help will be appreciated.

[Kaper]

As these are sums, so the results are ADDITIVE.

You can use SUMIFS to have sum from sheet1 and add it to a result of SUMIFS function for second Sheet.

SUMIFS can have a number of conditions tested, so you can use it also for the second part of your question - with dates. If you encounter problems with writing dates into formulas, you may want to use DATE function, so in the part where you test columnE, use

Please Login or Register  to view this content.

As for dynamic ranges - you can use whole columns as ranges in SUMIFS, or even better - keep your data in excel tables (you declared Excel 2013 as used version)

[raghusri]

thnaks and regards