Using = or > or < or >= or <= in a Formula based on Drop-Down Entry

[rilzniak]

Hey geniuses!

I have a drop-down which I'm using right now for a data set that has two columns I'm counting/summing/averaging. I'm calculating a count for the range but want to add a twist with one of the operator symbols (=, >, <, >=, <=) as part of the formula but it doesn't want to work for me. Here's an edited version of my count formula:

=COUNTIFS ( A2:A100 , "A" , D2:D100 , $C$2 & $D$2 , E2:E100 , $F$2 & $G$2 )

C2 is a drop-down with =, >, <, >=, <= as options
D2 is a drop-down with 1 to 11 options
F2 is the same as C2
G2 is 1 or 2

I'll also be applying this same formula to a sum and an average once I have this figured out. Thanks in advance.

 

[Toadstool]

Hi Rilnziak,

It seems to work for me (although I'm not sure you want to use D2 as the drop-down and have it as a part of the COUNTIFS)

Rilzniak.xlsx
	A	B	C	D	E	F	G	H	I
1
2	A		<	3	1	>=	2		2
3	A			1	2
4	A			2	3
5	A			3	4
6	A			4	5
7	A			5	6
8	A			6	7
9	A			7	8
10	A			8	9
11	A			9	10
12	B			10	11
Sheet1

Cell Formulas
Range	Formula
I2	I2	=COUNTIFS(A2:A100,"A",D2:D100,$C$2&$D$2,E2:E100,$F$2&$G$2)

Cells with Data Validation
Cell	Allow	Criteria
C2,F2	List	>,<,>=,<=,=
D2	List	1,2,3,4,5,6,7,8,9,10,11

 

[rilzniak]

Hi Rilnziak,

It seems to work for me (although I'm not sure you want to use D2 as the drop-down and have it as a part of the COUNTIFS)

Rilzniak.xlsx
	A	B	C	D	E	F	G	H	I
1
2	A		<	3	1	>=	2		2
3	A			1	2
4	A			2	3
5	A			3	4
6	A			4	5
7	A			5	6
8	A			6	7
9	A			7	8
10	A			8	9
11	A			9	10
12	B			10	11
Sheet1

Cell Formulas
Range	Formula
I2	I2	=COUNTIFS(A2:A100,"A",D2:D100,$C$2&$D$2,E2:E100,$F$2&$G$2)

Cells with Data Validation
Cell	Allow	Criteria
C2,F2	List	>,<,>=,<=,=
D2	List	1,2,3,4,5,6,7,8,9,10,11

Opened up a new workbook and tried your example and it worked perfectly. So...I try it again in the original workbook I had by copying the formula and it worked. Sigh. I can't be bothered to figure out what went wrong.

Thank you, Toadstool, you wizard!