COUNTIF CONDITIONS

[jeremiah256]

I'd appreciate help with the following problem:

I have to build a spreadsheet and it has to look at two conditions.
Count the total number of instances when the items in column A are "X"
and the items are column B are "Y".

An Example:

I'm a superintendant and all 20 of my schools have sent their annual
report. The report lists the schools names, total number of students,
the individual student names, their grades and whether they are male
or female.

Based on their info, I have to build a spreadsheet in the following
format:

#A's male #A's female #B's male #B's female (and so on)
H.S. One
H.S. Two
H.S. Three
H.S. Four

CountIf seems to not to like to work with 'AND' or 'IF'. Is this
possible in Excel?

Thanks

Jerry

 

[Norman Harker]

Hi Jerry!

A short tutorial:

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.


--
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.

 

[Peo Sjoblom]

Try sumproduct to count with multiple criteria, i.e.

=SUMPRODUCT(--(A2:A50="condition1"),--(B2:B50="condition2"),--(C2:C50="condi
tion3"))

 

[Chris Leonard]

Picked this out from a recent post ....

For what I had set up, that SUMPRODUCT formula did the
trick! Thanks a million guys.

-----Original Message-----
Derek

Try this:

=SUMPRODUCT((MONTH(A2:A200)=12)*(C2:C200="Y"))

Andy.




.

Hope it helps you

Also try looking at array formulas

Chris

 

[jeremiah256]

All,

Thanks for the info on the SUMPRODUCT function. It's just what I needed.

Jerry