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

F

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
 
C

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.
Click to expand...

1. =SUMPRODUCT((B2:B100="Manager 1")*(G2:G100<>""))
2. =SUMPRODUCT((B2:B100="Manager 1")*(G2:G100=""))


Regards
Claus Busch
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count from Blank & Non-Blank Cells 2
Excel Concatenate with line breaks but ignore blank cells 3
counting blank cells within two columns 2
Leaving a formula cell blank when data has not yet been added. 5
Counting Non Blank Cells 4
Counting Cells with Multiple Range Criteria (Excel 2003) 7
Count max consecutive cells 2
Need Formula - If blank cells 3

Top