Faster calculation instead of sumifs with three criteria in Excel 2007

Don't reference entire columns like this: =SUMIFS(DATA!$N:$N;DATA!$C:$C;$A4;DATA!$AB:$AB;E$1;DATA!$S:$S;$C$2)
Instead you should do this: =SUMIFS(DATA!$N$2:$N$107;DATA!$C$2:$C$107;$A4;DATA!$AB$2:$AB$107;E$1;DATA!$S$2:$S$107;$C$2)
The problem with this is that when you add rows you need to update the formulas.

The solution is to use the Excel Table feature (under the Insert tab, to the left). One of the advantages with this is that the Table will expand automatically when new data is added below the last row, no need to update the formulas.
In this sheet I converted your data to Excel Table and redid the formula in cell E3 to this: =SUMIFS(Table1[QTY],Table1[CODE],$A3,Table1[MONTH],E$1,Table1[PRICE],$C$2)

It is very easy to change the formulas. You can use find and replace (Ctrl + h) or you can just select the part of the formula you want to change and then select the Table column by moving the cursor carefully the top of that column and click when you see the black arrow pointing down. Sometimes it will select the normal column but then you have gone a little to high, just try again.



http://www.contextures.com/xlExcelTable01.html
http://www.techrepublic.com/blog/10-things/10-reasons-to-use-excels-table-object/
http://chandoo.org/wp/2013/06/26/introduction-to-structural-references/

Here is a solution for you using a table for the data and a formula that uses table nomenclature.

The formula in Calculation!E3. Drag across and down:

Formula:

Please Login or Register  to view this content.

The [[ ]:[ ]] form absolute references in a formula referencing a table.

@Jacc

Looks like we had pretty much the same idea for the same reasons, with a different implementation procedures.

I think you made a typo in the formula choosing Price instead of Series...no big deal

Formula:

Please Login or Register  to view this content.

Instead of dragging this formula, select the row from E3:AS3 then use Ctrl R to fill in the formula along the row. Then double click the fill handle to fill down.

Gotcha!

Please Login or Register  to view this content.

How come you know this?

Did you have tried it?

If so, please post the code, so other forummembers can use them also.