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