Excel

[Glenn]

Can I put two criteria in one formula for eg.

=countif(B:B,"<500")

this formula will look in column B and tell me how many
are under 500 but what I need is to add another criteria
to this formula to say <500 and >200

 

[Mark Graesser]

Glenn
Here are two options

1) Count of less than 500, minus count of less than or equal to 200

=COUNTIF(B:B,"<500")-COUNTIF(B:B,"<=200"

2) SUMPRODUCT builds 2 arrays of 1's (true) and 0's (false), multiplies the corresponding components between the arrays, and sums the products

=SUMPRODUCT((B1:B1000<500)*(B1:B1000>200)

Two issues to be aware of. The array sizes of the two (or more) arguments must be equal. You cannot use column references (B:B)

Good Luck
Mark Graesse
(e-mail address removed)


----- Glenn wrote: ----

Can I put two criteria in one formula for eg

=countif(B:B,"<500"

this formula will look in column B and tell me how many
are under 500 but what I need is to add another criteria
to this formula to say <500 and >20

 

[Norman Harker]

Hi Glenn!

You've already had answers to very similar questions

Try:
=COUNTIF(B:B,">"&200)-COUNTIF(B:B,">="&500)

Since you keep asking, here's some explanation.

COUNTIF and SUMIF are awkward functions and don't accept multiple
conditions using (e.g.) AND or OR in the first argument. But in your
case we can use two separate COUNTIF functions.

=COUNTIF(B:B,">"&200) will count all cases above 200 (note that 200
itself won't be counted).
we then deduct all cases over or equal to 500 and that gives us the
count for the range.

Note that I have had to put the quotations marks in the formula around
the > and >=. I have also had to use the & to join it with the desired
border (200 or 500).



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.