Advanced SUMPRODUCT question

[apiekar]

I have historically used SUMPRODUCT as a way to manipulate arrays to provide certain data in sort of an advanced vlookup type of fashion. For instance I would do something like =sumproduct((A1:A5="xyz")*(B1:B5="ABC")*(C1:C5)).

Behind the scenes, excel's calculations would end up being something like =sumproduct({0;0;1;0;1}*{0;0;0;0;1}*{1;2;3;4;5}), with the resulting being '5'.

What I am trying to do now, is create something that depending on a certain condition, it produces a particular formula, sort of like an advanced IF statement.

For instance, something like

=sumproduct((A1:A3="XYZ")*(if(x>y,0);if(x=y,1);if(x < y,2))
<y,2))

However, this formula generates an error, it doesn't work.

Any help would be much appreciated

Thanks.</y,2))

 

[Aladin Akyurek]

Care to provide an example what should this do?