Sumproduct UDF function in VBA

[YasserKhalil]

Hello everyone
I am trying to return a value based on three criteria ..
Here's my try

Please Login or Register  to view this content.

When running the code I got an error "Type mismatch"
Here's the attachment
Thanks advanced for help

[mikerickson]

What is the result that you are looking for?
It looks like COUNTIFS would do what you want.

[YasserKhalil]

I want simply to depend on three criterias : first in column A - Second in Column B - Third in column C and finally return the value n column D
I can use one of the following formulas tp do the task

Please Login or Register  to view this content.

or the array formula

Please Login or Register  to view this content.

[Bernie Deitrick]

Your formula string is 304 characters long - the limit is 255. Get rid of the (external:=True) and your code should work.

Instead of a macro, you could use this in cell G7

=GetData(G3, G4,G5)

Or use

=SUMIFS(D:D,A:A,G3,B:B,G4,C:C,G5)

and skip the VBA altogether.

[YasserKhalil]

Thanks a lot Mr. Bernie for this wonderful help
Actually the modification of the UDF function by removing External=true solved the problem

But there is still a problem for me ::: the criteria may occur more than once .. in this case all solutions failed as the solutions sum the values and I don't need to sum .. In this case I need to return the first occurence only
The only successful formula for that task is the array formula

Please Login or Register  to view this content.

Now I need to convert this formula to UDF function as this is a part of another complicated code ..

[Bernie Deitrick]

Please Login or Register  to view this content.

[YasserKhalil]

Thank you very very much for this wonderful and awesome solution
Thanks a lot for offering help
Kind Regards