Printable View
Hi All,
Maybe this is an old topic on the multi condition. I found that we all almost use SumProduct for multi-condition matter.
I just wander whether we have any other way to solve the multi conditions? as sumproduct is really use a huge resource. Everytime I open the sheet which is full of sumproduct, it take 1 minutes to recalculate.
I heard somewhere on array function. Do you know how to use it?
Thansk in advance.
An array function tends to be even more resource hungry than SUMPRODUCT.
How about a vba function? Multi-condition is a daily use methods.
But sumproduct seem to not leverage the advantage of re-use data.
Especially when we use it in mass:for example we calculate on same database (same conditions colume, sum column)but just change criteria. With sumproduct, it has to re-load the database everytime we call for it.
THanks for promt reply XLD
Either helper columns or VBA is usually the answer in these cases, but I don't have enough info to be more explicit.
Sorry for late in rely XLD, please find attached file for more information.
For case 1: I have to use a lot of sumproduct function, and I looking for another alternative function.
For case 2: I looking for a multi-criteria lookup function.
Hope it is clear
here come case 2
Case 1
Helper columns added. Is this any better?
Case 2
Another thing to at least consider is to use a pivot table to summarize your data.
The 'Data' sheet is not really setup for the most flexible way to use a PT since each month is in a seperate column (instead of a single column called 'Month'), but take a look
A PT needs to be Refreshed when the data changes instead of just have Excel reclculate
Paul
A different methodology
[vba]
Option Explicit
Option Base 1
Sub TestSpeed()
Dim arrData
Dim arrNeed
Dim arrPackCat()
Dim rwData As Long, Rws As Long, i As Long
Dim x As Long, y As Long
Dim Rng As Range
'get data ranges
With Sheets("Need")
Set Rng = Range(.Cells(7, 1), Cells(Rows.Count, 1).End(xlUp)).Resize(, 14)
End With
Rng.Columns("C:N").ClearContents
arrNeed = Rng
With Sheets("Data")
arrData = Range(.Cells(2, 4), .Cells(Rows.Count, 4).End(xlUp)).Resize(, 19)
End With
'Clear old data
'create concatenated list
Rws = UBound(arrNeed)
ReDim arrPackCat(Rws)
For i = 1 To Rws
arrPackCat(i) = arrNeed(i, 1) & arrNeed(i, 2)
Next
'Read from Data, check destination row & add to array position
rwData = UBound(arrData)
For i = 1 To rwData
x = Application.Match(arrData(i, 1) & arrData(i, 2), arrPackCat, 0)
For y = 1 To 12
arrNeed(x, y + 2) = arrNeed(x, y + 2) + arrData(i, y + 7)
Next
Next
'Write result to sheet
Rng = arrNeed
End Sub
[/vba]
If you did reformat it as a list, the PT feature might offer some more options for you
Paul
Thanks Mack,
For case 1, your solution is exactly what I'm looking. Thanks so much.
Just to expand the topic:
This is quite unconvinced, so I have to try to use array function, the purpose is to avoid building additional column in data sheet:
- As there are 5 criteria in the data sheet, so there are dozens of possible criterion group.
- With this way of merging all criteria into 1 criterion, for each criteria group we have to have build want additional column.
= {SUMIF(Data!$D$1:$D$300&Data!$E$1:$E$300,Need!$Q$7:$Q$307,Data!$K$7:$K$307) }
But it did not work.
For case 2: It is in the same concern, because I can not edit the datasheet. In the file I sent, the datasheet is in the same file, but actually, it is a separated file with more than 200sheets.
@Paul:
Please find attached for your more info. Data base function and pivot table dont give me the flexibility in structure the report, So I have to use other function.