Need Array Formula for Counting Blank & Non Blank Cells (Multiple Criteria)

[foofoo]

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

 

[Ron Rosenfeld]

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)

=SUMPRODUCT(($B:$B="Manager 1")*($G:$G=""))

 

[Claus Busch]

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

 

[Ron Rosenfeld]

=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).