Remember Me?

 greg7468 Posts: n/a Counting blank and filled cells within a range.

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

--
greg7468

------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031

 Fred Posts: n/a 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

--
greg7468

------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031

 B. R.Ramachandran Posts: n/a 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

--
greg7468

------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031

 B. R.Ramachandran Posts: n/a 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).
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

--
greg7468

------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post LMB New Users to Excel 3 April 29th 05 02:55 AM Tasi Excel Discussion (Misc queries) 5 March 29th 05 10:48 PM Marc Todd Excel Worksheet Functions 1 January 26th 05 09:58 PM JackRnl Excel Worksheet Functions 3 December 20th 04 01:11 PM

All times are GMT +1. The time now is 09:44 AM. Copyright ©2004-2021 ExcelBanter.