COUNTING WITH CRITERIA LINKED TO A DROP DOWN LIST

Paul Riley

New Member
Joined
Feb 20, 2004
Messages
41
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
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
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 20, 2004
Messages
41
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
Joined
Feb 28, 2003
Messages
2,615
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,859
Messages
5,544,710
Members
410,631
Latest member
JFORTH97
Top