Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula to evaluate multiple criteria (countif + and)
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula to evaluate multiple criteria (countif + and)
=SUMPRODUCT((B1:B100<100000)*(C1:C100100))
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "VPSales" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula to evaluate multiple criteria (countif + and)
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 "VPSales" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting COUNTIF for multiple criteria in multiple columns | Excel Worksheet Functions | |||
Excel: Using DSUM, I need to evaluate 2 criteria that are not toge | Excel Worksheet Functions | |||
How do I COUNTIF in excel using multiple criteria? | Excel Worksheet Functions | |||
Countif formula with multiple criteria ie >30 and <60? | Excel Worksheet Functions | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions |