Since column C has multiple names per cell we'll need to tweak that a bit.

=SUMPRODUCT(--(MONTH(A1:A10)=10),--(B1:B10="reported"),--(ISNUMBER(SEARCH("john",C1:C10))))

Note that it's possible to get "false positives" when using SEARCH. SEARCH
will look for the substring *anywhere* within the string. So, if you're
looking for John it will be found in words like Johnson or Johnston or St.
Johns. Being that your names are first names this wll probably not be an
issue.

--
Biff
Microsoft Excel MVP


"Dave Peterson" wrote in message
...
First, the formula will only work if you're using xl2007 (or higher). You
can't
use the entire column in earlier versions.

Second, the * (multiplication symbol) means that you're "anding" each of
these
criterial. It's looking for values in each row where the month in column
A is
10, the value in column B is Reported and the value in column C is John.

So if you have Ralph in C1, then row 1 will be ignored (or treated as 0 in
the
count).

A equivalent formula that doesn't need to be array entered
(ctrl-shift-enter):

=sumproduct(--(month(a1:a10)=10),--(b1:b10="reported"),--(c1:c10="john"))

Adjust the ranges to match--but you can't use whole columns (except in
xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

===================
And if you ever had to count the number of January's, then this:

=sumproduct(--(month(a1:a10)=10),
--(b1:b10="reported"),
--(c1:c10="john"))

would become:

=sumproduct(--(month(a1:a10)=1),
--(isnumber(a1:a10)),
--(b1:b10="reported"),
--(c1:c10="john"))

(In fact, it's probably a good idea to include that check no matter what
month
you're checking--it would be easy to forget to modify the formula when
checking
for January!)

=month(a1)
equals 1
if A1 is empty.

Empty cells will return 1


JRD wrote:

Thanks, does this formula pick out John in column C, or does Column C
have to
have only John in it for this to work?

"Otávio Alves Ribeiro" wrote:

Hi there.
One possible solution could be:

=SUM((month(A:A)=10)*(B:B="Reported")*(C:C="John") ) SHIFT + CTRL +
ENTER

Please, note that you must end your formula by pressing SHIFT + CTRL +
ENTER, as this formula is an array one.

Regards,
Otávio

"JRD" wrote:

Example:
A B
C
1 01/10/2009 Cancelled John,
Steven
2 01/09/2009 Reported John,
Steven
3 20/09/2009 Cancelled John,
Darren
4 12/10/2009 Reported John,
Darren, Steven
5 14/10/2009 Reported
Darren, Steven
How can I count the number of rows that contain month of October in
column A, Reported in column B and contain John in column C - in
this example the answer is 1 (row 4)

Thanks


--

Dave Peterson