Conditional Count (Array Formula?)

[Debbie Mason]

Hello all,

I am trying to create a weekly summary page on a monthly missed pickup
report that counts the number of times a missed pickup appears (given codes
1-3) for a certain driver (name) within a date range. This is what I've
figured out so far:

=COUNTIF(K4:K33,"james")*AND(COUNTIF(M4:M33,"1")*AND(COUNTIF(E4:E33,"1")))

This works halfway, but the data I need summarized is broken down into three
codes (the second countif section - choice of 1, 2, or 3). The third countif
section is my way of separating the weeks of the month (1-4). Currently, this
formula is counting even just the appearance of the name as 1 entry in
addition to the actual data codes I want counted. I may have gone the long
way around the mountain to do this, but I am stuck trying to figure out a way
to get this done. Ideally, I'd like to just put in a date range and name as
conditions and a code to search for and count.

Thanks in advance.

 

[T. Valko]

Try this:

A1 = start date
B1 = end date
C1 = James
D1 = code (1, 2 or 3)

=SUMPRODUCT(--(E4:E33>=A1),--(E4:E33<=B1),--(K4:K33=C1),--(M4:M300=D1))

Biff

 

[JLatham]

Try
=SUMPRODUCT(--($K$4:$K$33="james"),--($M$4:$M$33,=1),--($E$4:$E$33=1))
You can change the various parameters to look for other people, other codes
(2, 3) besides 1 and other weeks of the month.
You could even make it flexible and refer to values in another set of cells
to look data up for others in one cell. Example: you put the formula above
on the same sheet with the data in E, K and M in it- in A1 you type in a
name, in B1 you put in one of the codes (1 2 or 3) and in C1 you put in the
week of the month to look at: then in A2 you put in
=SUMPRODUCT(--($K$4:$K$33=A1),--($M$4:$M$33,=B1),--($E$4:$E$33=C1))
and A2 will give you a count of the matches of all 3 entries on rows 4:33.

 

[Debbie Mason]

Y'all are awesome! Thanks so much!!
--
Debbie

Try
=SUMPRODUCT(--($K$4:$K$33="james"),--($M$4:$M$33,=1),--($E$4:$E$33=1))
You can change the various parameters to look for other people, other codes
(2, 3) besides 1 and other weeks of the month.
You could even make it flexible and refer to values in another set of cells
to look data up for others in one cell. Example: you put the formula above
on the same sheet with the data in E, K and M in it- in A1 you type in a
name, in B1 you put in one of the codes (1 2 or 3) and in C1 you put in the
week of the month to look at: then in A2 you put in
=SUMPRODUCT(--($K$4:$K$33=A1),--($M$4:$M$33,=B1),--($E$4:$E$33=C1))
and A2 will give you a count of the matches of all 3 entries on rows 4:33.