Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 27th 05, 03:05 PM
greg7468
 
Posts: n/a
Default 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

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


  #2   Report Post  
Old June 27th 05, 04:25 PM
Fred
 
Posts: n/a
Default

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


  #3   Report Post  
Old June 28th 05, 04:46 PM
B. R.Ramachandran
 
Posts: n/a
Default

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


  #4   Report Post  
Old June 28th 05, 10:41 PM
B. R.Ramachandran
 
Posts: n/a
Default

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return number of cells filled LMB New Users to Excel 3 April 29th 05 02:55 AM
Applying formula to only NON-EMPTY cells in range Tasi Excel Discussion (Misc queries) 5 March 29th 05 10:48 PM
Eliminate creating list that returns blank cells Marc Todd Excel Worksheet Functions 1 January 26th 05 09:58 PM
Blank cells in a chart JackRnl Excel Worksheet Functions 3 December 20th 04 01:11 PM


All times are GMT +1. The time now is 09:44 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017