# Possible to use INDIRECT to insert greater than, less than, etc?

#### mighty mouse

##### Board Regular
Is it possible to use INDIRECT to insert greater than, less than, equals into a formula?

i.e.

Cell B1 contains the ">" sign
Cell B2 contains "5"

=sumproduct(--(indirect("a1:a10"&b1&b2)),--(c1:c10="Yes"))

The above formula gives me #REF

Any help is appreciated.

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### pgc01

##### MrExcel MVP
No. Indirect just performs indirection, not evaluation.

If you want to use Sumproduct(), you can use a number of ifs or a lookup to choose the type of comparison. It won't be complex, just long.

There is a much simpler solution, to use a database function, in this case DCOUNT(A)(). I'd rearrange the layout to have a table and use in the criteria B1&B2.

#### pgc01

##### MrExcel MVP
P. S.

I'm assuming you don't work with excel 2007 or you could just use COUNTIFS().

#### mighty mouse

##### Board Regular
Thank you pgc. I will look into dcount(a)(). I don't use 2007 (yet).

#### pgc01

##### MrExcel MVP
I've done an example with the options I mentioned.

Using the Database function, the criteria are

H4: =F4&F3
I4: Yes

The formula using DCount,

F6: =DCOUNT(B3:C12,1,H3:I4)

Using Sumproduct(), for ex.,:

F8: =SUMPRODUCT(--(CHOOSE(MATCH(F4,{"=","<>",">","<",">=","<="},0),B4:B12=F3,B4:B12<>F3,B4:B12>F3,B4:B12<F3,B4:B12>=F3,B4:B12<=F3)),--(C4:C12="Yes"))<?XML:NAMESPACE PREFIX = F3,B4 /><F3,B4:B12>

Using CountIFs (excel 2007):

=COUNTIFS(B4:B12,F4&F3,C4:C12,"Yes")

I'd choose the CountIFs() if I had excel 2007 and DCount() for previous versions. DCount() forces you, however, to have a criteria range.

</F3,B4:B12>

Last edited:

Replies
0
Views
281
Replies
3
Views
1K
Replies
6
Views
124
Replies
4
Views
109
Replies
8
Views
545

1,191,216
Messages
5,985,319
Members
439,957
Latest member
khaled shafy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back