Aggregate function with multiple conditions gives runtime error

[Falck99]

Hi,

I have the 2016 Excel version where Minifs is not available. Instead, I'm trying to use the aggregate function to identify the minimum value of range C1:C4 in Sheet1 if two criteria are met (Range B1:B4 in Sheet1 equals value in cell B1 and Range A1:A4 equals value in cell A1). I am able to get the correct result if I "print" the formula in a cell in Sheet1, but get a runtime error 13 (type mismatch) when I specify the ranges in vba. I've written the following code:

Sub test()

Sheets("Sheet2").Range("a1") = Application.Aggregate(15, 6, Sheets("Sheet1").Range("c1:c4") / ((Sheets("sheet1").Range("b1:b4") = Sheets("sheet1").Range("b1")) * (Sheets("sheet1").Range("a1:a4") = Sheets("sheet1").Range("a1"))), 1)


End Sub


Would greatly appreciate if someone could point out the error, as I've now completely exhausted Google searching for an answer.

Thanks!

 

[dircur]

Perhaps
Sheets("Sheet2").Range("a1") =
Sheets("Sheet2").Range("a1").Value =

 

[Falck99]

Perhaps
Sheets("Sheet2").Range("a1") =
Sheets("Sheet2").Range("a1").Value =

Thanks, but it still gives me the same error message

 

[dircur]

Any of your source formatted as text?
Can you successfully write the formula in cell for just 1 cell?
and also,
Application.Aggregate(
Application.Worksheetfunction.Aggregate(

I will stop pretending after this. (that I know much of anything on Aggregate)

 

[Falck99]

Any of your source formatted as text?
Can you successfully write the formula in cell for just 1 cell?
and also,
Application.Aggregate(
Application.Worksheetfunction.Aggregate(

I will stop pretending after this. (that I know much of anything on Aggregate)

Everything with "General" format in Sheet1. Not entirely sure that I understand what you mean about writing the formula in cell. If I write the following in formula in excel (not using vba), it works as it should:

=AGGREGATE(15;6;C1:C4/((B1:B4=B1)*(A1:A4=A1));1)

I suspect that the error is related to the ranges in the vba code..

Also tried both application and application.worksheetfunction. Makes no difference unfortunately.

Appreciate you for trying to help

 

[kvsrinivasamurthy]

Try this code

Sub test()
Sheets("Sheet2").Range("a1") = Evaluate("aggregate(15,6,'Sheet1'!C1:c4/(('Sheet1'!b1:b4='Sheet1'!B1)*('Sheet1'!a1:a4='Sheet1'!a1)),1)")

End Sub

 

[Falck99]

Try this code

Sub test()
Sheets("Sheet2").Range("a1") = Evaluate("aggregate(15,6,'Sheet1'!C1:c4/(('Sheet1'!b1:b4='Sheet1'!B1)*('Sheet1'!a1:a4='Sheet1'!a1)),1)")

End Sub

Thank you so much kvsrinivasamurthy! Works like a charm