Microsoft Excel Help Required

[Steve ( West Mids )]

I'm trying to do a count as below ( actually want to do it on dates but can't get it to work on numbers ) ...... pretty easy to explain

A1 - 5

B1 - 4

B2 - 5

B3 - 6

C1 - in here I want to count the number of entries in B1:B3 that are less than A1 ( answer should be 1 ).

I've tried countif but it won't recognise the A1 cell, will work with a constant though or = to a cell.

Any ideas? It's quite a complex spreadsheet so i can't go setting other cells first :(

[Dave-Ellen]

hi,

pm me tomorrow if nonone can help you tonight. I'm working with a wizard in excel and may be able to help

[Whitebeam]

=IF(B1<A1,1,0)+IF(B2<A1,1,0)+IF(B3<A1,1,0)

[Steve ( West Mids )]

Idea is good Whitebeam - thanks. Unfortunately as it's a complex worksheet would be a nightmare to set up and maintian ( over 100 rows by 100 columns ) and may hit size limits on formula.

thanks

[Whitebeam]

OK - hadn't fully understood the problem.

See what you mean about COUNTIF.

=FREQUENCY(B1:B10,A1)

is close except that it includes the case where values are equal - i.e. <=, not just <. You could use the this and reference A2 instead of A1 where A2 = A1-1.

Other option is to add another column that holds 1 if less than, 0 if equal or above and sum the column.

Good luck. Excel is wonderful most of the time and a pain in the a** for the rest.

[Whitebeam]

I'm a bit slow tonight:

=FREQUENCY(B1:B10,A1-1)

[Steve ( West Mids )]

Cheers that looks great ....... I'll try it on Monday :)