Combine SUM, INDEX and MATCH

If I'm understanding correctly, the code would be:

Please Login or Register  to view this content.

I am genuinely struggling to come up with a faster construction. Like, Boolean SUMPRODUCT might do it, but I think it would be tough to validate?

I agree with dosydos -- using arrayed Lookups with INDEX(MATCH) would for sure be in the neighborhood of 1000x slower.

Are you required to use a worksheet formula? This is the kind of thing that looks like it might be better accomplished using a pivot table (https://www.excel-easy.com/data-****...ot-tables.html ). Obviously, your sample is a much smaller database than the real one, but the procedure should be the same:

1) Select source (A2:C10 in your sample file -- if you select one cell in your source, Excel will usually expand the selection as best as it can guess).
2) Insert->Pivot Table. Confirm choices for source data and output cell/range.
3) I put MVA-Code as row labels, MVA-term as column labels, and sum of Line-Sum in the values field for the pivot table.
4) (optional) Select the column label dropdown and filter the results for MVA-term 6 only.

Where the main concern is computation speed, the big advantage of a pivot table for this is that pivot tables are very fast -- almost always executing much faster than worksheet formulas. That's probably the direction I would be going for something like this -- figure out how to use a pivot table for the task.

That's a good point Shorty, but I mean, we're dealing with only 10,000 rows. I'm not sure that's a big enough dataset for a relatively, very simple SUMIFS to even care. I kinda feel like there's probably something else going on, and this behavior with SUMIFS is only the symptom of some greater root cause.

Like when I populated it up to 10,000ish rows of garbage data using INT(RAND()*10) for all three columns, I could mash F9 and the 30 SUMIFS updated without even blinking. Actually, that's probably worth checking -- OP, how many SUMIFS total are you running here?

I can't use a Pivot table because I need the result other places that can't access a Pivot. So, I need to do this by code.
And I would like that code to be a combination of SUM, INDEX and MATCH. Can someone please help me with such a code?

Originally Posted by abrj

And I would like that code to be a combination of SUM, INDEX and MATCH.

If your problem is that SUMIFS is too slow,
cash money you will not fix that problem with some arrayed monstrosity of SUM, INDEX, and MATCH; you will for sure have an even worse performance problem.

Can you post the exact syntax of the SUMIFS you are using? I feel like you might be catching a marching range (eg, $A$1:A1000 or something) or something equally arcane, that is your actual source of pain.

Another thought:

Do $C$3:$C$15000, $A$3:$A$15000 and $B$3:$B$15000 also contain formulas?
Might there be some recalculations happening due to volatile functions there or elsewhere?