Home 
Search 
Today's Posts 
#1




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




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




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




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


Similar Threads  
Thread  Forum  
Return number of cells filled  New Users to Excel  
Applying formula to only NONEMPTY cells in range  Excel Discussion (Misc queries)  
Eliminate creating list that returns blank cells  Excel Worksheet Functions  
Blank cells in a chart  Excel Worksheet Functions 