SUM Lookup Function???

[demon8991]

I am trying to sum a large data set based on a multiple criteria.

So imagine I have a data tab with ID’s 1 – 1000 in Column A each of these have a piece of data associated with them in column B.

On a separate lookup tab I want a summed lookup function entered into one cell that can find the sum of Column B based on a set of user defined ID’s there could be up to 450 choices not necessarily in order.

My current solution involves having a line for each user defined ID and a lookup formula in each just pulling back the corresponding figure and then having a sum at the top of the column but obviously this then means I have a ridiculous amount of lookups just to obtain one total summed number and based on the scale I am using this file it makes it incredibly big.

Thanks in advance

[Max, Singapore]

Try SUMIF
Assume your source data in Sheet1, IDs in col A, values in col B
In the other tab, you have IDs running in A2 down
Put in B2: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B)
Copy down
--------
Success? Wave it, hit the little star at the bottom left of my response

[demon8991]

Try SUMIF
Assume your source data in Sheet1, IDs in col A, values in col B
In the other tab, you have IDs running in A2 down
Put in B2: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B)
Copy down
--------
Success? Wave it, hit the little star at the bottom left of my response

This works but does the same as the vlookup a I have in there? Would it be a more efficient calculation and keep file size down?

What I was after was a formula that did those individuals calculations and summed all the values all in one cell.

[Max, Singapore]

Ah, think I misunderstood it earlier
You could try something like this, array-entered (ie press CTRL+SHIFT+ENTER to confirm the formula):
=SUM(IF(COUNTIF(MyR1,Sheet1!$A$2:$A$100),Sheet1!$B$2:$B$100))
where MyR1 is a defined range containing a subset of items within the source range: Sheet1!$A$2:$A$100
--------
Success at last? Wave it, hit the little star at the bottom left of my response

[demon8991]

That formula works a treat as the file reduces by 30mb.

The problem I have now is because the file has so many calculated cells it takes 10 minutes to do a calculation on an i5 processor.

Is there a way I can get my result by using VBA code as I read this is a much more efficient way of doing things?

[Max, Singapore]

> ... That formula works a treat as the file reduces by 30mb
That's good to hear, that it works for you ... and with the side benefits

> ... because the file has so many calculated cells it takes 10 minutes to do a calculation on an i5 processor
well, that's the side effect of using array formulas.
(a) You could try reducing the data ranges in the formula to the smallest extent just large enough to cover. The smaller the range, the faster the calcs.
(b) Consider using manual calc mode. Press F9 only when it is required to calc, eg on completion of all data entry (by batches) or setting of params (DVs for example). I do use this manual mode quite a fair bit, and it makes things manageable/tolerable

> ... Is there a way I can get my result by using VBA code as I read this is a much more efficient way of doing things?
I don't know. I do understand that vba runs slower than formulas. My 2 cents views
Do a posting in programming forum if you seek this route

---------------------
Any worth? Yes? Whack the little star at the bottom left of my responses