Counting blank and filled cells within a range.

[greg7468]

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.

[Fred]

Greg,

One way:
in C2 =if(isblank(B2),"Blank","Data")
copy down col C

Use a pivot table with ColA as rows, colC as columns, count of ColB as data.

HTH

"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
>
>

[B. R.Ramachandran]

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
>
>

[B. R.Ramachandran]

Hi,
Thre was a typo in my earlier response.
The second formula (i.e., for filled cells) near the bottom of the message
should go to cell C16 (and not B17).
Sorry about that.
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
>
>