Hi Emma!
You may be able to use more that one COUNTIF to achieve your multiple
criteria:
=COUNTIF(A1:A10, ">5") - COUNTIF(A1:A10, ">=7")
Or you can use implicit if statement structures in SUMPRODUCT:
=SUMPRODUCT((A1:A10>5)*(A1:A10<7))
In this case note that A1>5 and A1<7 will evaluate as 1 or zero and
will only add to the product if both are 1.
Or you may prefer the explicit IF function approach that uses SUM
=SUM(IF(A1:A10>5,1,0)*IF(A1:A10<7,1,0))
Entered as an array by pressing and holding down Ctrl + Shift and then
pressing Enter.
Appears as:
{=SUM(IF(A1:A10>5,1,0)*IF(A1:A10<7,1,0))}
COUNTIF is used to count based upon a single criteria but does not
allow use of OR or AND as criteria to allow multiple criteria.
SUMPRODUCT allows counting with multiple criteria and uses arrays but
does not require entry as an array although strictly speaking it is
still an array formula. If you want to count how many cases there are
with non-zero values in two columns use:
=SUMPRODUCT((A1:A10>0)*(B1:B10>0))
The structure (A1:A10>0) can be regarded as an implicit IF function.
The formula tests each pair A1 & B1, A2 & B2, etc. in turn and sums
the results. Taking the first pair. IF A1 is greater than 0 then A1>0
returns TRUE and if B1>0 it also returns TRUE. The SUMPRODUCT function
coerces these Boolean values of TRUE
and FALSE to the values 1 and 0. 1 * 1 = 1 and as part of its sum of
the results of each pair of multiplications it adds 1.
Looking at the first pairing in the ranges, internally it has looked
at the same as =(A1>0)*(B1>0). There are four possible combinations of
comparison of A1 and B1, 1*1, 1*0, 0*1 and 0*0. Only if A1 and B1 are
greater than 0 will a comparison resolve to 1.
The problem is that (quite logically) we often try to use COUNTIF but
unfortunately that function does not accept multiple criteria using
(e.g. AND or OR).
To compare strings in cells we might use the same principle but here
we must put the string in double quotes or Excel will go off and look
for named cells or formulas.
=SUMPRODUCT(('My Sheet'!$A$1:$A$100="x")*('My Sheet'!$B$1:$B$100="y"))
Note that ('My Sheet'!$A$1:$A$100 = "x") and ('My Sheet'
!$B$1:$B$100="y") are implicit IF statements. SUMPRODUCT evaluates the
multiplication of the returns of each of these statements with TRUE
evaluating to 1 and FALSE to 0. The effect is the same as having a
pair of columns with 1's or 0's in each column with SUMPRODUCT summing
the results of multiplying each pair together. Since 0*0 = 0 and 0*1 =
0 and 1*0 = 0 and 1 * 1 = 1 the multiplication of a pair will only add
to the count if both elements return TRUE.
Another way, handles multiple criteria in any column to count another
column by criteria. Pointed out by JE McGimpsey is to use the
following approach:
=SUMPRODUCT((A1:A100={"N","E"})*(B1:B100="John"))
Note the array type parentheses in the formula are manually entered.
This is a case of a formula using an array rather than an array
formula. It's non-array entered and counts (in this case) all cases
where N and E in column A coincide with the name John in column B. So,
for example we might set it up to count how many sales John has made
in N and E regions.
--
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.
