Count values with conditions - again

[ElvisS]

In the beggining of this week I posted a situation with Excel formulas
- I was trying to count values with certain conditions. See
http://groups.google.com/group/micr...a26a28985b56f8?lnk=arm&hl=en#a2a26a28985b56f8

I got what I wanted (count the number of people in one month), thanks
for all the help.

Now I'm asking for help again, because I have a different situation. I
want to count the number of people in variable period. Let's say from
23.3.2006 to 23.3.2006. Previous solution was focusing on month, now I
need a formula for changeable period.

See
http://groups.google.com/group/micr...a26a28985b56f8?lnk=arm&hl=en#a2a26a28985b56f8

Thanks in advance!

Elvis

 

[Pete_UK]

This was the formula that Bob gave to you:

=SUMPRODUCT(--(MONTH(A2:A20)=4),--(B2:B20="John"),--(C2:C20="Smith"))

You can think of the three expressions within the SUMPRODUCT brackets
being linked as ANDs, i.e. the month =4 AND first name = John AND last
name = Smith. So now you want to change it so that date is greater than
a certain value AND less than another value AND first name is .... etc.
The two dates you have quoted are the same, but assume they might be
different for a generic solution:

=SUMPRODUCT(--((A2:A20)>=DATEVALUE("23/3/2006")),--((A2:A20)<=DATEVALUE("24/3/2006"),--(B2:B20="John"),--(C2:C20="Smith"))

By using DATEVALUE it is easy to see what dates apply - in this case
23/3/2006 to 24/3/2006 inclusive.

Hope this helps.

Pete

 

[Bob Phillips]

Adding to Pete's excellent summary, I tend to use a straight date string,
but using the ISO standard format to remove any US.rest of the world date
ambiguity. so I would write it like so

=SUMPRODUCT(--(A2:A20>=--"2006-03-23"),--(A2:A20<=--"2006-03-24"),--(B2:B20=
"John"),--(C2:C20="Smith"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)