Multi-condition function

[yurble_vn]

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.

[Bob Phillips]

An array function tends to be even more resource hungry than SUMPRODUCT.

[yurble_vn]

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

[Bob Phillips]

Either helper columns or VBA is usually the answer in these cases, but I don't have enough info to be more explicit.

[yurble_vn]

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

[yurble_vn]

here come case 2

[mdmackillop]

Case 1
Helper columns added. Is this any better?

[mdmackillop]

Case 2

[Paul_Hossler]

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

[mdmackillop]

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]

[Paul_Hossler]

If you did reformat it as a list, the PT feature might offer some more options for you

Paul

[yurble_vn]

Thanks Mack,
For case 1, your solution is exactly what I'm looking. Thanks so much.

Just to expand the topic:

• 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.

This is quite unconvinced, so I have to try to use array function, the purpose is to avoid building additional column in data sheet:
= {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.