counting based on 2 conditions
 

Hi again.

Range A1:10 has either "Full" or "Flex" as their contents.
they indicate whether a sales rep is full time or flex
time.

Say Column B has their name, or it may have Vacant instead
of a name if no one has yet been hired for that territory.

I like to write a formula that looks in the range A1:a10
and B1:10 and gives me the count of how many are Flex that
ARE NOT Vacant.

In other words give me a count of Flex that are not vacant.

How do you do that?
Thanks everyone....

=SUMPRODUCT((A1:A10="Flex")*(B1:B10<"Vacant"))

I'm leading a FREE 1-hour online Webinar on Excel Tips & Tricks.
Dec 16, Jan 14, Jan 27 from 4-5PM (each session is the same).
If interested, go to http://www.iil.com, click on the yellow/orange
"Try a free webinar" link on the left side, click the Microsoft Excel
Tips & Tricks link, follow instructions to register.

"Thrava" wrote in message
...
Hi again.

Range A1:10 has either "Full" or "Flex" as their contents.
they indicate whether a sales rep is full time or flex
time.

Say Column B has their name, or it may have Vacant instead
of a name if no one has yet been hired for that territory.

I like to write a formula that looks in the range A1:a10
and B1:10 and gives me the count of how many are Flex that
ARE NOT Vacant.

In other words give me a count of Flex that are not vacant.

How do you do that?
Thanks everyone....

Hi
=SUMPRODUCT(--(A1:A10="Flex"),--(B1:B10<"Vacant"))

also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

"Thrava" schrieb im Newsbeitrag
...
Hi again.

Range A1:10 has either "Full" or "Flex" as their contents.
they indicate whether a sales rep is full time or flex
time.

Say Column B has their name, or it may have Vacant instead
of a name if no one has yet been hired for that territory.

I like to write a formula that looks in the range A1:a10
and B1:10 and gives me the count of how many are Flex that
ARE NOT Vacant.

In other words give me a count of Flex that are not vacant.

How do you do that?
Thanks everyone....

Guys thank you very very much for this.
You both are saviors :)


-----Original Message-----
Hi again.

Range A1:10 has either "Full" or "Flex" as their contents.
they indicate whether a sales rep is full time or flex
time.

Say Column B has their name, or it may have Vacant
instead
of a name if no one has yet been hired for that territory.

I like to write a formula that looks in the range A1:a10
and B1:10 and gives me the count of how many are Flex
that
ARE NOT Vacant.

In other words give me a count of Flex that are not
vacant.

How do you do that?
Thanks everyone....
.