Solved: help: How to get cells data for calculation in the best way

[yurble_vn]

I use the following code for a function to sum data with multi conditions and flexible conditions. But when called a lot of time, the calculation become very slow. I dont know whelther there is any "stupid code" I have used and How to effectively do it. I mean what is the best practice way.

For more details, let me give an example for what I try to do with this code:

it is to sum cells in colume named "Colmonth" that satisfy:

• the value in the same row at column "colLocation" is equal to "Location"
• the value in the same row at column "colCategory" is equal to "CAT"
• the value in the same row at column "colFlavor" is equal to one of the value in given range named "KeyRange"

What I'm worrying about is the way I get value of range and compare it.
And also about get value in given range (keyRange). Because it is in a different sheet in comparison with other

Please help!!!
[vba]
cntKeyRange = keyRange.Count ' Number of cells in given range
With Sheets(nmeSheet)
For icount = pstLocation To pstLocation + MaxLine 'search range
If .Range(colLocation & icount).Value = Location And .Range(colCategory & icount).Value = CAT Then
For jcount = 1 To cntKeyRange
If .Range(colFlavor & icount).Value = keyRange.Cells(jcount).Value Then
sum = sum + .Range(Colmonth & icount).Value
GoTo Continue
End If
Next
End If
Continue:
Next
End With[/vba]

[mdmackillop]

Can you post your workbook so that speed/alternatives can be tested, and without an idea of size, we don't know whether to look at filtering rather than looping, or other possible solutions.

[unmarkedhelicopter]

Your KeyRange may have to be extended to cover a bigger range, but you should be able to do this with a sumproduct formula or an array.

[Bob Phillips]

It is probably slowing because of the repeated recalculation of a range of cells.

Change it to be event driven and only recalculate any that are impacted by changed cells.

[Norie]

Why not just use a worksheet function, something like SUMPRODUCT perhaps, rather than a user defined function?

[yurble_vn]

Here is the attached file.

It is probably slowing because of the repeated recalculation of a range of cells.

Change it to be event driven and only recalculate any that are impacted by changed cells.

HOw to do that?

Why not just use a worksheet function, something like SUMPRODUCT perhaps, rather than a user defined function?

Because excel did not have sum multi condition with multi choice condition, I mean:

• Sum if (equal to one value) and (equal to one of, let say, 10 value)

Do you have any idea for this kind of sum, I'm still new with excel.

[Bob Phillips]

Because excel did not have sum multi condition with multi choice condition, I mean:

• Sum if (equal to one value) and (equal to one of, let say, 10 value)

Do you have any idea for this kind of sum, I'm still new with excel.

=SUMPRODUCT(--(A2:A20="value 1"),--(B2:B20=number2))

[yurble_vn]

Perhaps I have not expressed my idea in the right way:
By saying equal one of the 10 values, I means:

Sum if (Ai="Value1") and (Bi =any in {Value2,Value3,Value4,....})

with Value2,Value3,Value4,.... is given through a range


thanks for prompt reply

[Bob Phillips]

=SUMPRODUCT((A2:A13="Value1")*(B2:B13={Value2,Value3,Value4}))

[yurble_vn]

Thanks xld,

Is it better and defined function when I have 3500 records? and this kind of calculation will be used everywhere in my worksheet? (more than 20.000 calls)

SOrry for such a stupid question. Cause I dont have any idea about the benchmark between excel function and defined function.

once again, thanke

[Bob Phillips]

To be honest, it depends.

Is the function referencing the same ranges all of the time? Is part of tha data more volatile than the rest? And so on. The answers determine whether better to stick with built-ins, or maybe use event code. The big problem with event code is that say you have a range A1:A2000 on sheet 2 that are perecedent cells on 3000 cells on sheet1, you would have to monitor the sheet2 cells using the event code (very efficient), but then loop through 3000 cells recalculating their value (not so efficient).

Not a simple question to answer I am afraid.

[Norie]

I might be wrong but I would think that using Excel's inbuilt functionality eg worksheet functions would be faster/better than trying to use code, or as some say reinvent the wheel.

[yurble_vn]

BTW: How the following code works? when there is only 1 value in {}, it's okie, but not when there are more than 2

[VBA]

=SUMPRODUCT((A2:A13="Value1")*(B2:B13={Value2,Value3,Value4}))

[/VBA]

[Bob Phillips]

I might be wrong but I would think that using Excel's inbuilt functionality eg worksheet functions would be faster/better than trying to use code, or as some say reinvent the wheel.

There are many circumstances where you would be wrong.

Array formulae are expensive, and many of them are slow, so VBA can be a better option.

[yurble_vn]

Help please:
[VBA]=SUMPRODUCT((A2:A13="Value1")*(B2:B13={Value2,Value3,Value4}))[/VBA]

HOw to get this code work?when there is only 1 value in {}, it's okie, but not when there are more than 2

[Bob Phillips]

This works fine, I have tested and re-tested

=SUMPRODUCT((A2:A13="Value1")*(B2:B13={1,2,3))

But use your head, if it is strings you want, quote enclose them

=SUMPRODUCT((A2:A13="Value1")*(B2:B13={"Value2","Value3","Value4"}))

[yurble_vn]

Thanks, maybe rushing make me make mistake everywhere.

By the way, is there anyway to replace {"Value2","Value3","Value4"} by a range ?

[unmarkedhelicopter]

Maybe, I said this in post number 3.
The problem (I don't think it is a problem really) with arrays and sumproduct is that the ranges have to be the same size as they are processed cell by cell and in one of the ranges doesn't have (say) cell 617 then the calculation falls over.
Having said that you are probably best including an extra column with an array formula determining if each row contains someting from your keyrange and then running a sumproduct to get your result based on this.

[unmarkedhelicopter]

An example of such a search formula is :-
=OR(NOT(ISERROR(FIND(UPPER(searchvaluesrange),UPPER(item)))))

This is an ARRAY formula.

[Bob Phillips]

Of course

=SUMPRODUCT((A2:A13="Value1")*(ISNUMBER(MATCH(B2:B13,myRange,0))))