Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Array Formula for Counting Blank & Non Blank Cells (Multiple Criteria)
Hello -
I am using Excel 2003. I need to use an array formula to determine the following 2 conditions: 1. The number of times a manager's name shows up in Column B and the number of non blank cells in column G. 2. The number of times a manager's name shows up in Column B and the number of blank cells in column G. An example of my data is: Manager (column B) Date Trng Comp (column G) Manager 1 02/08/12 Manager 2 02/09/12 Manager 3 Manager 2 02/01/12 Manager 1 Manager 1 02/02/12 For Manager 1, I need to know how many non blank cells are in column G (there are 2) and how many blank cells are in column G (there is 1) Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Array Formula for Counting Blank & Non Blank Cells (Multiple Criteria)
On Mon, 20 Feb 2012 08:44:46 -0800 (PST), foofoo wrote:
Hello - I am using Excel 2003. I need to use an array formula to determine the following 2 conditions: 1. The number of times a manager's name shows up in Column B and the number of non blank cells in column G. 2. The number of times a manager's name shows up in Column B and the number of blank cells in column G. An example of my data is: Manager (column B) Date Trng Comp (column G) Manager 1 02/08/12 Manager 2 02/09/12 Manager 3 Manager 2 02/01/12 Manager 1 Manager 1 02/02/12 For Manager 1, I need to know how many non blank cells are in column G =SUMPRODUCT(($B:$B="Manager 1")*($G:$G<"")) (there are 2) and how many blank cells are in column G (there is 1) =SUMPRODUCT(($B:$B="Manager 1")*($G:$G="")) Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Array Formula for Counting Blank & Non Blank Cells (Multiple Criteria)
Hi,
Am Mon, 20 Feb 2012 08:44:46 -0800 (PST) schrieb foofoo: 1. The number of times a manager's name shows up in Column B and the number of non blank cells in column G. 2. The number of times a manager's name shows up in Column B and the number of blank cells in column G. 1. =SUMPRODUCT((B2:B100="Manager 1")*(G2:G100<"")) 2. =SUMPRODUCT((B2:B100="Manager 1")*(G2:G100="")) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Array Formula for Counting Blank & Non Blank Cells (Multiple Criteria)
On Mon, 20 Feb 2012 12:20:52 -0500, Ron Rosenfeld wrote:
For Manager 1, I need to know how many non blank cells are in column G =SUMPRODUCT(($B:$B="Manager 1")*($G:$G<"")) (there are 2) and how many blank cells are in column G (there is 1) =SUMPRODUCT(($B:$B="Manager 1")*($G:$G="")) I forgot you are using Excel 2003. That precludes referencing a full column in an array formula; so the above ranges need to be changed from $B:$B and $G:$G to =$B$2:$B$max_row_num and =$G$2:$G$max_row_num where max_row_num is the highest row number you might encounter (and could be 65536). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for counting number of blank cells in spreadsheet by row | Excel Discussion (Misc queries) | |||
Counting blank cells in a formula | Excel Worksheet Functions | |||
Advanced Filter for multiple criteria, including blank cells | Excel Worksheet Functions | |||
=SUMPRODUCT formula is counting the blank cells as well as zero's | Excel Worksheet Functions | |||
=SUMPRODUCT formula is counting the blank cells as well as zero's | Excel Worksheet Functions |