Hi,
Assuming that your sample data block is in Row 2 to 13, try the
following formulas.

for xblank, =SUMPRODUCT(($A$2:$A$13="x")*ISBLANK($B$2:$B$13))
for xfilled, =SUMPRODUCT(($A$2:$A$13="x")*ISNUMBER($B$2:$B$13))

If you have a large number of labels in Column A, then it would be more
convenient to list them somewhere in the sheet and referencing them in the
formulas. For instance, for your sample data, you could set up the result
block such the the following.

A B C
Row 15 Label Blank Filled
Row 16 x
Row 17 y
Row 18 z

The formula in B16 would be:
=SUMPRODUCT(($A$2:$A$13=A16)*ISBLANK($B$2:$B$13))
and in B17,
=SUMPRODUCT(($A$2:$A$13=A16)*ISNUMBER($B$2:$B$13))
Now, you can fill-in the formulas to the rows beneath Row 16 (here, Rows 17
and 18).
Regards,
B. R. Ramachandran



"greg7468" wrote:


Hello all,
I need help to count the following.

- col A --- col B
-- x ------ blank
-- x ------ blank
-- x ------ data
-- y ------ data
-- y ------ data
-- y ------ data
-- y ------ blank
-- z ------ data
-- z ------ blank
-- z ------ blank
-- z ------ blank
-- z ------ data

Can someone please supply me with the formula that will count the
amount of blanks and filled cells for each value in column A.

I need to create the following table

x blank = 2
x filled = 1

y blank =1
y filled =3

z blank =3
z filled = 2

Thank you for your help in advance.


--
greg7468


------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031
View this thread: http://www.excelforum.com/showthread...hreadid=382493