COUNTING WITH CRITERIA LINKED TO A DROP DOWN LIST

[Paul Riley]

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

 

[Jon von der Heyden]

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]

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]

Hi,

Try DCOUNT. See..

Book1
	A	B	C	D	E	F
1	Field1	Field2		Field1	Field2	Count
2	3	2		>=3	<=4	5
3	9	9
4	6	1
5	2	1
6	5	4
7	9	7
8	7	8
9	6	3
10	1	5
11	7	7
12	3	8
13	5	8
14	0	3
15	4	4
Sheet2

Formula in F2,

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

I used drop down list in D2 and E2.

HTH

 

[Paul Riley]

Hi Krishnakumar,

Thanks for this - it works fine.

Paul