# COUNTING WITH CRITERIA LINKED TO A DROP DOWN LIST

#### Paul Riley

##### New Member
Hi,

I have the count function:

SumProduct((\$A\$6:\$A\$6450=BT2)*(\$BA\$6:\$BA\$6450=BT3)

and I want to interrogate the spread sheet using drop down lists in cells BT2 and BT3.

This fine when the lists have numbers (ie 1,2,3,4,5,6 etc) but I would like to the count for example:

BT2 >3 and BT3 <4

When I have >3 and <4 in the lists the count function becomes:

SumProduct((\$A\$6:\$A\$6450=>3)*(\$BA\$6:\$BA\$6450=<4)

Which, of course, gives an error.

Is there a way to modify this without getting into VBA?

Thanks,

Paul

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### Jon von der Heyden

##### MrExcel MVP, Moderator
What type of error are you receiving? What you have seems perfectly plausable to me so I'm unsure why you having an error, unless there are errors in either A6:A6450 or BA6:BA6450?

And when you say dropdown list assume you mean combobox linked to BT2 and BT3?

Reagrds,
Jon

#### Paul Riley

##### New Member
Hi Jon,

If in cell BT2 I have 6 (either typed in or selected frpm a list)
and in cell BT3 I have the values:

1 the count function returns 49
4 the count function returns 19
7 the countfunction returns 6

So the countfunction works OK but when cell BT3 has:

>1 or >2 or >3 the countfunction always returns 0
Is the countfunction getting confused with = and >?

Thanks Paul

PS the countfunction works fine if I manually type in the > sign rather than the = sign eg:

SumProduct((\$A\$6:\$A\$6450=3)*(\$BA\$6:\$BA\$6450>4) This is OK so it looks like the function is not correctly recognising the >4 when it tries to pick it up from cell BT3

#### Krishnakumar

##### Well-known Member
Hi,

Try DCOUNT. See..
Book1
ABCDEF
1Field1Field2Field1Field2Count
232>=3<=45
399
461
521
654
797
878
963
1015
1177
1238
1358
1403
1544
Sheet2

Formula in F2,

=DCOUNT(A1:B15,2,D1:E2)

I used drop down list in D2 and E2.

HTH

#### Paul Riley

##### New Member
Hi Krishnakumar,

Thanks for this - it works fine.

Paul

Replies
10
Views
157
Replies
3
Views
173
Replies
2
Views
31
Replies
2
Views
63
Replies
5
Views
77