Counting rows that satisfy multiple conditions
 

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

Counting rows that satisfy multiple conditions
 

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

Counting rows that satisfy multiple conditions
 

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

Counting rows that satisfy multiple conditions
 

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

Counting rows that satisfy multiple conditions
 

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

Counting rows that satisfy multiple conditions
 

Hmmm.

I read this sentence:

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?

To mean that each cell in the range/column would have to contain John.

I would guess that your interpretation is probably better <vbg.

"T. Valko" wrote:

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

--

Dave Peterson

Counting rows that satisfy multiple conditions
 

What about this one?

Example:
A B C D
1 01/10/2009 Cancelled John, Steven 4
2 01/09/2009 Reported John, Steven 2
3 20/10/2009 Reported John, Darren N/A
4 12/10/2009 Reported John, Darren, Steven 2
5 14/10/2009 Reported Darren, Steven 4
6 15/10/2009 Reported John, Darren 2
How can I average the numbers in column D, but only the ones in rows where A = October, B = reported and C contains "John"?. Note that there are some text strings in column D, so it is not all numbers - the answer for the example would be 4+2 divided by 2 = 3

Thanks


"T. Valko" wrote:

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


.

Counting rows that satisfy multiple conditions
 

Try this array formula** :

=AVERAGE(IF(MONTH(A1:A10)=10,IF(B1:B10="reported", IF(ISNUMBER(SEARCH("john",C1:C10)),D1:D10))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"JRD" wrote in message
...
What about this one?

Example:
A B
C D
1 01/10/2009 Cancelled John, Steven
4
2 01/09/2009 Reported John, Steven
2
3 20/10/2009 Reported John, Darren
N/A
4 12/10/2009 Reported John, Darren, Steven
2
5 14/10/2009 Reported Darren, Steven
4
6 15/10/2009 Reported John, Darren
2
How can I average the numbers in column D, but only the ones in rows
where A = October, B = reported and C contains "John"?. Note that there
are some text strings in column D, so it is not all numbers - the
answer for the example would be 4+2 divided by 2 = 3

Thanks


"T. Valko" wrote:

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


.