Count values with conditions - again
 

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/micro...a26a28985b56f8

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/micro...a26a28985b56f8

Thanks in advance!

Elvis

Count values with conditions - again
 

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

ElvisS wrote:
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/micro...a26a28985b56f8

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/micro...a26a28985b56f8

Thanks in advance!

Elvis

Count values with conditions - again
 

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)

"Pete_UK" wrote in message
oups.com...
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

ElvisS wrote:
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/micro...a26a28985b56f8

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/micro...a26a28985b56f8

Thanks in advance!

Elvis