Counting Using Multiple Criteria

[mhall5]

Does anyone know what is the best way to count values based on multipl
criteria? I am trying to count each instance where based on a certai
range of dates, a person name shows up. What would be the best way t
do this. I have tried countif, count(if(, Sumif, array formulas
sumproduct,etc. All of these to no avail. If anyone has any suggestion
or thinks I am overlooking something please let me know. THe colum
which contain the dates and names of persons are not ajacent. Any nam
can be corresponded with any date, even identical. Please help!!! Als
is it possible to enter date ranges as criteria in exce

 

[Biff]

Hi!

Assumptions:

Dates in A1:A100
Names in Z1:Z100

is it possible to enter date ranges as criteria in excel

Use 2 cells to hold the date range and a cell to hold the name:

B1 = start date
C1 = end date
D1 = name

=SUMPRODUCT(--(A1:A100>=B1),--(A1:A100<=C1),--(Z1:Z100=D1))

Biff

 

[Bob Phillips]

=SUMPRODUCT(--($A$2:$A$200>=--"2006-01-01"),--($A$2:$A$200<=--"2006-01-31"),
--($X$2:$X$200="Pete"))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

This should get the results you want:

=SUMPRODUCT(--(A1:A100="Name"),--(C1:C100>DATEVALUE("1/01/2005")),--(C1:C100<DATEVALUE("01/15/2005")))

Of course, adjust the ranges and dates to match your needs.

HTH,
Elkar

 

[mhall5]

THanks for all the replies guys. I think that last one finally got it
for me. But I want to thank everyone who replied to my post.