Counting rows that satisfy multiple conditions

[JRD]

Example:

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

 

[Otávio Alves Ribeiro]

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]

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?

 

[Dave Peterson]

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 here:
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

 

[T. Valko]

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.

 

[Dave Peterson]

Hmmm.

I read this sentence:

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

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

 

[JRD]

What about this one?

Example:

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





.

 

[T. Valko]

Try this array formula** :

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

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