Excel Formula to evaluate multiple criteria (countif + and)

[VPSales]

I am trying to evaluate a data base of customers where column A lists the
customers by name, column B is annualized sales $, and column C is the
manhours of service per year spent on that account. I want to know how many
customers I have which generated less than $100,000 of sales, and which
required more than 100 manhours of service. I have tried every variation of
"Countif " combined with "and" that I can think of , for example:
=countif(and(b1:b100,"<"&100000),(c1:c100,">"&100))
This is a simplified example, but this is the solution I need. Can anyone
help?

 

[Luke M]

=SUMPRODUCT((B1:B100<100000)*(C1:C100>100))

Notes about SUMPRODUCT: Prior to 2007, you can not use an entire column as
an array (but you can use an entire row...go figure?). Array sizes must all
be the same. As you may guess, you can easily add more criteria to this
function if you wish.

 

[Ashish Mathur]

Hi,

Assume that the data is arranged as follows in range C9:E14

Name Sales manhours
A 123456 123
S 6746 123
D 89023 23
F 5679 99
A 6689 67

In C16:E16, enter Name,Sales,manhours. In D17, enter <100000 and in E18
enter >100. In cell C17, enter =DCOUNTA($C$9:$E$14,C16,D16:E17)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com