In Cell G4 I make a SUMIF based on the column criteria in Cell G1 and the row criteria in Cell G2 using this formula:

=SUMIF($A$1:$A$7,$G$1,INDEX($A$1:$E$1,MATCH($G$2,$A$1:$E$1,0)))

All this works fine.

However, in my original file I have the issue now that the dataset is much larger compared to this example.

In the example the columns only go until 2024-04.

In my original file I have
(a) much more years on a monthly basis and
(b) much more products and therefore much more rows.

This leads to the situation that the loading time when using the formula significantly increases.

Do you know if there is any alternative approach/formula which has the same functionality but does not effect the loading times as much?

• excel
• excel-formula

• 1 1) I don't understand how your formula even outputs the correct result, but it does!; 2) You could try something like this: =SUM(($B$2:$E$7)*($B$1:$E$1=$G$2)*($A$2:$A$7=$G$1)), but I can't tell if it's faster or not. How big is your actual data set that you're getting slow responses? – Excellor Commented Apr 2 at 7:56
• 2 What do you mean by loading time? The sheet opening or the execution? – Black cat Commented Apr 2 at 8:48

1 Answer 1

=SUMPRODUCT((A2:A7=G1)\*(B1:E1=G2)\*B2:E7)

1. SUMPRODUCT processes arrays efficiently without needing multiple lookup functions.

2. Works well if the dataset is structured properly.