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))
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))