Count a range of cells using an IF

A

Access Joe

Hey everyone,

Excel 2000

I have a range of cells like so (each number is in it's own cell)
50 20 30 60 90 30

The FIRST cell (in this case, "50") is the 'recommended value'. What I want
to do is create a function that looks at all the other 'variable' cells (20,
30, 60....) as one whole range and checks to see if any one of them are less
than the recommended value of 50. If so, I want to see a count of how many
'variables' were less than 50. So in this example, I would like to see the
answer of "3", because there are three variables in that range of cells that
are less than 50.

Hope that makes sense. Assuming it would be some kind of IF / COUNTIF, but
can't figure out how to do it.

Thanks so much!
J
 
P

Pete_UK

Assuming your data is in row 1, with the value 50 in A1, you can put
this in a different row:

=COUNTIF(1:1,"<"&A1)

If the data is in column A, starting with A1, then put this in a
different column:

=COUNTIF(A:A,"<"&A1)

Hope this helps.

Pete
 
P

Pete_UK

Max,

As A1 can never be less than A1 then it will not be included in the
count, so you can include it in the range as per your first post.

Pete
 
R

robzrob

Errata, range should exclude recommended value in A1, viz:
In F1: =COUNTIF(B1:E1,"<"&A1)
--
Max
Singaporehttp://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik
---
Click to expand...

Why isn't it =COUNTIF(B1:F1,"<A1") ???
 
P

Pete_UK

If you include the A1 within the quotes it will be taken as the
literal string "A1" rather than the contents of A1. The OP did have 6
numbers, so the range should include F1.

Hope this helps.

Pete
 
M

Max

Yes. But I was thinking it's a clearer way to implement the criteria
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik
---
Max,

As A1 can never be less than A1 then it will not be included in the
count, so you can include it in the range as per your first post.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If and Countif 2
If with countif or counta 3
MROUND Help 1
Drop Downs, Index Match or what? 56
COUNTIFS (the numbers in a column, when subtracted meet a certain criteria) 1
Average problem 1
Formula not working properly 2
Sliding Commission Formula 2

Top