Conditional Counting using worksheet function..

[allanTeh]

I have 2 Columns A and B with following values:

Values in Cells A2 to A11 are:
1.30
1.35
1.31
1.41
1.40
1.31
1.41
1.30
1.40
1.35

Values in Cells B2 to B11 are:
4
4
3
4
4
4
3
2
2
1

The result I need is :

If range in Column A (A2 to A11) = 1.31 to 1.40,
count the NO OF CELLS in Column B (B2 to B11) where the value = 4.

QUESTION: How to use worksheet function to do it?

(The correct answer should be 3)

 

[Lewiy]

Try this:

=SUMPRODUCT(--(A1:A10<=1.4),--(A1:A10>=1.31),--(B1:B10=4))

 

[ADAMC]

Try:

=SUMPRODUCT(--(A1:A10<1.5),--(A1:A10>1.30),--(B1:B10=4))

 

[allanTeh]

BOTH answers given above NOT working !!

Is it because cell format have to be of certain type to make the answer given SUMPRODUCT to work?

 

[Scott Huish]

With the possible exception of numbers formatted as text, no.

But you should adjust for your actual range:
=SUMPRODUCT(--(A2:A11<=1.4),--(A2:A11>=1.31),--(B2:B11=4))

Note however that you can't use entire columns with SUMPRODUCT.