Greater or less than in cell to use in formula

[Numbers123987]

Hi, I am for a few days stuck in looking for a solution to fill in the greater than or less than symbols(>, <) in a cell so that it is easily adjust the result of a sumproduct formula.
=sumproduct(--(choose(B3;C5:C25>0;C5:25<0));--(D5:D25>25%);--(E5:E25))
B3 = 2 (1 stands for greater than, 2 stands for less than)

Above formula is resulting in #Value!

Is it possible to use a cell for < and > (or 1 and 2) so that it is adding it to a sumproduct formula in combination with choose?
If yes, what am I doing wrong?

Any help as very much appreciated!

 

[Eric W]

Welcome to the forum!

How about:

=SUMPRODUCT(--(C5:C25>0),--(D5:D25>25%),E5:E25)*(B3=1)+SUMPRODUCT(--(C5:C25<0),--(D5:D25>25%),E5:E25)*(B3=2)

 

[Numbers123987]

Welcome to the forum!

How about:

=SUMPRODUCT(--(C5:C25>0),--(D5:D25>25%),E5:E25)*(B3=1)+SUMPRODUCT(--(C5:C25<0),--(D5:D25>25%),E5:E25)*(B3=2)

Magic! Thank you very much indeed!
Should have joined earlier

Cheers,
Eric H

 

[joeu2004]

=SUMIFS(E5:E25, C5:C25, CHOOSE(B3, ">0", "<0"), D5:D25>25%)

or

=IFERROR(SUMIFS(E5:E25, C5:C25, CHOOSE(B3, ">0", "<0"), D5:D25>25%), 0)

The latter covers instances when B3 is neither 1 nor 2, if that's possible.

 

[Eric W]

=SUMIFS(E5:E25, C5:C25, CHOOSE(B3, ">0", "<0"), D5:D25>25%)

Nice option, but I think you mean:

=SUMIFS(E5:E25, C5:C25, CHOOSE(B3, ">0", "<0"), D5:D25,">25%")

 

[Numbers123987]

Thanks joeu and Eric,

For the sumifs it works when using CHOOSE(B3, ">0", "<0"). However the reason why I started using sumproduct was that the zero's are sometimes also other numbers and therefore also referring to cells. I tried SUMIFS(E5:E25, C5:C25, CHOOSE(B3, ">", "<")&D3, D5:D25,">25%") but that was without success.
D3 was the swap for 0.

 

[CA_Punit]

What is wrong with your formula.. It is working i suppose with Choose function.

Book1
	A	B	C	D	E
1			1	18%	1
2			-1	20%	2
3			2	30%	3
4	Criteria
5	2	0
6
Sheet1

Cell Formulas
Range	Formula
B5	B5	=SUMPRODUCT(--CHOOSE(A5,C1:C3>0,C1:C3<0),--(D1:D3>25%),--(E1:E3))

Help me if i made a mistake.

 

[Numbers123987]

No you didn't make a mistake. With sumproduct solution of Eric it is working fine.
However I was questioning if the formula was also possiible with sumifs and in particular with cell reference instead of <0?
like: SUMIFS(E5:E25, C5:C25, CHOOSE(B3, ">", "<")&D3, D5:D25,">25%")
D3 is in the example changing the 0.

 

[CA_Punit]

No you didn't make a mistake. With sumproduct solution of Eric it is working fine.
However I was questioning if the formula was also possiible with sumifs and in particular with cell reference instead of <0?
like: SUMIFS(E5:E25, C5:C25, CHOOSE(B3, ">", "<")&D3, D5:D25,">25%")
D3 is in the example changing the 0.

Your Question for Post 1.
The formula is working with Sumproduct & Choose as solution provided by Mr ERIC

also from Post 8, sumifs will work using choose function with constraint changing in cell D3. It is working in my case perfectly

 

[Numbers123987]

Thanks CA Punit! yes you are right. With sumifs it works as well (I had a typo in my previous formula that I did not see).
Thanks all for the help, very much appreciated!