Alternative to vlookup or sum array in order to speed up calculation

[daseffects]

I'm currently using the arrary below to sum up matches in each respective column starting w/ column C. However, any time new values are pasted into the match range the calculation slows to a snails pace as I guess the match reruns or the sum recalcs. I've tried setting the calculation mode to manual when pasting and the back to automatic after the paste w/ no success in speeding up the overall calc.

Any thoughts one other ways to deal w/ adding up a variable number of matches?

{SUM(IF($B$70:$B$4000=$AA7,C$70:C$4000,1))}

I've just seen geof's response to the vlookup query below though am not sure that writing a new function will deal w/ the issue above.

Any thoughts.

DAS

 

[Bowers74]

Try:

SUMPRODUCT(($B$70:$B$4000=$AA7)*(C$70:C$4000)*1)



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Zathras]

DAS, this sounds like an interesting problem, but I can't quite figure out what you are trying to do. Can you set up some small test data with maybe only 5 rows and indicate what the result is you want? It looks like you want add 1 wherever the data from column B doesn't match, otherwise you want to add some data from column C, but that doesn't sound like anything that would produce a practical result. What am I missing? Also, you said "starting w/column C" but I don't see any other columns referenced in your formula. Can you help us out here a little bit?

Mike, I threw some data in a spreadsheet to compare your formula with DAS's, and I can't get the same answer. What test data did you use?

 

[Bowers74]

You're right Zathras,

My formula is WAY off. That's what I get for trying to do something like this in my head.

If I understood DAS correctly, he wants to have the sum of the values in column C in which the corresponding cell in column B matches the value in A77 and the number of non-matching cells in column C to column B added to this ammount.

The way I understood it is that DAS has multiple columns in which he is doing this and the Array Formula (as it will with large ammounts of data) is slowing him down.

I have two formulae, mainly because I am not 100% sure about DAS wanting to add the number of non-matching cells to the sum of the matches.

With the count added to the sum of the matches:

=SUMPRODUCT((B$70:B$4000=$A$77)*(C$70:C$4000))+SUMPRODUCT((C$70:C$4000<>$A$77)*1)

With just the sum of the matches:

=SUMPRODUCT((B$70:B$4000=$A$77)*(C$70:C$4000))


This is kind of a &quot;SUMVLOOKUP()&quot; function! The formulae can be autofilled to the left so that they work with the other columns that you want to check DAS! And they don't have to be entered as array formulae (which makes them faster). ;-)

That is is I understood you correctly!

I hope that this rektifies my mistake earlier!

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

daseffects,

Have you found another solution to this? If you have, then let us know.

;-)


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[combo]

DAS,
As I understand your formula, it can be also replaced by:
=SUMIF($B$70:$B$4000,$AA7,C$70:C$4000)+ROWS($B$70:$B$4000)-COUNTIF($B$70:$B$4000,$AA7)
where:
Sum of cells in col. (C) where col. (B)=AA7:
=SUMIF($B$70:$B$4000,$AA7,C$70:C$4000) (1)
Number of cells summed in (1):
=COUNTIF($B$70:$B$4000,$AA7) (2)
Total number of rows:
=ROWS($B$70:$B$4000) (3)

combo

 

[daseffects]

Thanks for all the comment on this.

I've gone w/ Mikes

=SUMPRODUCT((B$70:B$4000=$AAS$77)*(C$70:C$4000))

as it does speed up the calc a bit. Though the sum still slows down as new data is pasted into the table (see below).

To clarify a bit I'm looking to rollup (sum) all the values in a table (B70 to F4000) where their can be multiple occurances a row name in B to a table starting in AA70. So for example AA77 is product A and in each comun to the right I want to sum up all the product A values in the respective column in the larger table.

I've gone at it a few ways - though anytime the model pastes or changes values in the larger table the rolled up table calcs slowly.

Any other thoughts to speed this up gladly welcome.

D

 

[Nareik]

It involves a little bit of set up, but have yo thought about pivot tables.

Data entery will not involve any recalculation, however everytime you want to review the results, you will need to refresh the pivot table.

 

[DaleWatson123321]

daseffects,

I'd like to be able to demonstrate the power of Excel's &quot;database&quot; functions (e.g. =DSUM, =DCOUNTA, etc) in generating summary reports.

The best way to do this would be for you to email your file, or a scaled-down version - with any sensitive data replaced with fictitious data that still reflects the type of situation you're dealing with. I'll then modify the file and return it.

There are an increasing number of Excel users who have become quite surprised at the power of this relatively unused component of Excel.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca