# Faster calculation instead of sumifs with three criteria in Excel 2007

1. ## Faster calculation instead of sumifs with three criteria in Excel 2007

I am using Excel 2007. One sheet name is "data" & second sheet is "calculation"
In "data" sheet i have total 1,00,000 rows data.
In "calculation" sheet i have total 4000 rows to calculate (using sumifs formula)
My problem is its works slow & taking to much time for calculation.

In "data" sheet column "b" is invoice date, column "c" is code, column "n" is quantity, column "s" is series & column "ab" is month&date.
In "calculation" sheet i have column "a" code, column "b" customer name & from column "e" to "as" i calculate total sumifs with criteria code, month&date & series
I have attach sample file (with very less data).

I require vb micro for faster calculation.  Register To Reply

2. ## Re: 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/  Register To Reply

3. ## Re: Faster calculation instead of sumifs with three criteria in Excel 2007

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.  Register To Reply

4. ## Re: Faster calculation instead of sumifs with three criteria in Excel 2007

@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.  Register To Reply

5. ## Re: Faster calculation instead of sumifs with three criteria in Excel 2007

Gotcha!   Register To Reply

6. ## Re: Faster calculation instead of sumifs with three criteria in Excel 2007

I have going as per your formula in our original file. But i am sorry to say that, calculation processor running slow.
If those data run through vb micro with using create button in excel sheet, then calculation of data much faster.
Look once again.  Register To Reply

7. ## Re: Faster calculation instead of sumifs with three criteria in Excel 2007  Register To Reply

8. ## Re: Faster calculation instead of sumifs with three criteria in Excel 2007 ``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.  Register To Reply