0

Suppose in your workbook: Sheet1 has a table with 20 columns and approx 1000 rows. Each row belongs to one of 50 categories. The developer cannot make any visible changes to Sheet1.

Sheet2 has a cell where the user can enter an category ID, and is shown the result of a SUMIF on each of Sheet 1's columns.

My understanding is that Excel will iterate through all 1000 columns for each SUMIF, performing the same search 20 times. Is there an alternative to this?

WoodenKitty
  • 6,521
  • 8
  • 53
  • 73
  • I think you are asking to get efficient (more efficient idea than SUMIF). If so, there isn't anything better. The only thing you could consider is the way you put SUMIF result to Sheet2. At the moment you have it (I guess) as sheet formula like `=SUMIF(...)`. If, for any reason, you don't want that formula to exist you could apply them each time when you need new results with vba code using `WorksheetFunction.SumIf(...)` – Kazimierz Jawor Apr 12 '13 at 06:26
  • 1
    if it is all within excel there isnt really a more efficient way of doing this. If you could upload your data into a database and then query database and save results to a recordset then i am 99% sure it would have been more efficient –  Apr 12 '13 at 06:59
  • 1
    one more idea- pivot table... – Kazimierz Jawor Apr 12 '13 at 07:03
  • It might be worth noting that you can actually [connect to your worksheet using ADO](http://stackoverflow.com/questions/8756802/excel-function-to-make-sql-like-queries-on-worksheet-data) and run SQL on it. – wakjah Apr 12 '13 at 10:47
  • You could run a custom VBA subroutine whenever you detect that the selected category ID has changed (Worksheet_Change Event). Load your data range into an array, and keep running totals for each column, while looping through the array. Then, update the total values on sheet2 afterwards. Only requires one loop through the array. – Fink Apr 12 '13 at 19:24

1 Answers1

0

Here's what I've settled on. It definitely would not suit everyones' needs.

Situation:
 - I don't want to run SUMIF 20 times on 10000 rows
- I can safely assume there will never be more than 200 rows for each category (ie a group of rows that fulfil my sumif criteria).
- It's also useful for me to have a table with only the rows matching that criteria.

Solution:
 I create a table with 200 rows of formulas that will display rows that fit my criteria.

The leftmost column will have row number of the next matching row, or be blank and not search if there are no more.

First line of left column:

IFERROR(MATCH(CategoryID,'DataTable'!B:B,0),"")

Subsequent lines of left column (A2 is the cell above):

IF(A2="","",MATCH(CategoryID,OFFSET('DataTable'!B:B,A2,0,RowsInTable-A2,1),0)+A2))

All cells to the right of this are basic INDEX formulas that use the row number from the leftmost column.

To count the number of found results we use:

=COUNTA(A2:A202)-COUNTBLANK(A2:A202)

We then use the this count and the table to perform nice quick SUMs:

SUM(OFFSET(C:C,0,0,RowCount,1))
WoodenKitty
  • 6,521
  • 8
  • 53
  • 73