Hi,

I have table with columns like:
column1, "category_1"
column2, "category_2"
column3, "quantity"

In each row I want to have max (by category_1) from sum by category_1 and category_2.
By formulas I can done it by:
column4, "sum by cat1+cat2": =SUMIFS([quantity];[category_1];[@[category_1]];[category_2];[@[category_2]])
column5, "max by cat1 from sum by cat1+cat2": =MAXIFS([sum by cat1+cat2];[category_1];[@[category_1]])

At my real project, the value for current row at column5 (Maxifs) is calculated by VBA - it works well.

But the data sheet has several thousands of rows and SUMIFS calculations take lot of time.
So I tried calculate column4 values also by VBA. It sums correctly but it is being done only for one row so at the end calculation for column5 is not correct

I do not need values for column4 loaded to worksheet so may be there is some way to create one Maxifs formula by VBA that can calculate what I want in correct way?
maxifs1.PNGmaxifs2.PNG