countif and rank

[Shatin]

Would appreciate help with the following problem, which is a simplified
version of what I have to do.

Let's say I have a worksheet with 4 columns: A, B, C and D. Each column has
4 elements.

At the bottom of column A, the A5 cell, I want to put a formula which counts
the no. of cells in column A whose rank is among the lowest 3 in the A1 to
D4 range.

Thus if the A14 range is like this:

5 6 7 8
3 5 7 1
2 8 8 8
9 9 9 9

Then in A5, since 2 and 3 are among the lowest 3 in the range, the formula
will return the count of 2.

TIA.

 

[Bob Phillips]

Hi Shatin,

Here is a formula

=SUMPRODUCT(--(A1:A4=SMALL($A$1:$D$4,{1,2,3})))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Shatin]

Many thanks, Bob.

The formula works. What I don't understand is what does the -- mean in
=SUMPRODUCT(--
Can you please explain that to me?

TIA.

 

[Bob Phillips]

The formula in itself returns an array of True/ False, in the A1:A4 example
the array would be False, True, True, False. SumProduct cannot add these up,
so we need to coerce to a number (0/1) to do the addition. A mathematical
operation is required for this, so we could do 1*, but -- is the generally
accepted method of doing this. So -- changes the True/False array to a 1/0
array which SUMPRODUCT adds up, giving you the count.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Paul Robinson]

Hi
A two step solution if that is any good to you. I'm assuming all the
numbers in your table are positive

In cell A6 put in the formula
= MAX(0, A1-SMALL($A$1:$D$4,3))

Copy this across and down for the other numbers in your table.
Now count the 0's in this table. In Cell A10 put
= SUM(IF(A7:A10=0,1,0))

and enter as an array formula with Ctr + Shift + Enter. Copy this
formula across for the other 3 columns.

You could probably construct a single formula for each column using
the Offset function.

regards
Paul