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
Bookmarks