Please Help!!

A

ant

New Member
Joined
May 9, 2002
Messages
8
Hello all,

I hope this question makes sense. I have a column that is filled with numbers coming from an IF statement.

The IF statement either returns a value or "".

I want to count the number of cells that have values, and not the "", but I keep getting "" returned as a value.

Do any of you gurus know how to count just the cells with numbers in them and/or turn the "" into something else that is not counted in the COUNT formula???

THANKS!!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Hi Ant

You can use the counta() function

this function count only cells with values,text,or logical values (true or false)

Hope that helps
 
Upvote 0
something seems fishy here. You only want to count the numbers and by default count only counts numbers. Are you sure you're populated with a "" and your range is okay???
 
Upvote 0
I've been looking for guys like you!!

Thanks for the suggestions.

I believe my range is correct.

I think Count.if sounds right, but I must be employing it wrong since it is a new function for me.

Basically, I am trying to get a total count; I imagine the process would go:
1) Check the value in the cell.
2) If the value is "", dont count it, otherwise, count it and move to the next cell.
3) At the end of say 25 cells, I should have the total of all the cells with actual values.

Is this something for VBA or can the count.if statement check one cell, store its value, then move to the next...

Thanks again!
 
Upvote 0
On 2002-05-10 14:33, ant wrote:
I've been looking for guys like you!!

Thanks for the suggestions.

I believe my range is correct.

I think Count.if sounds right, but I must be employing it wrong since it is a new function for me.

Basically, I am trying to get a total count; I imagine the process would go:
1) Check the value in the cell.
2) If the value is "", dont count it, otherwise, count it and move to the next cell.
3) At the end of say 25 cells, I should have the total of all the cells with actual values.

Is this something for VBA or can the count.if statement check one cell, store its value, then move to the next...

Thanks again!
Click to expand...

<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=5><FONT COLOR=WHITE>Microsoft Excel - COUNT.xls</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=5>File(<U>F</U>) Edit(<U>E</U>) View(<U>V</U>) Insert(<U>I</U>) Tool(<U>T</U>) Data(<U>D</U>) Window(<U>W</U>) Help(<U>H</U>)</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>E5</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=2 BGCOLOR=White></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#008080 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FFFFFF>Numbers</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#008080 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FFFFFF>Result of Counting</FONT></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=IF(B2,1,"")')><FONT FACE=MS P???? COLOR=#000000></FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC99 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=COUNT(A2:A6)')><FONT FACE=MS P???? COLOR=#000000>3</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>1</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>3</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('=IF(B2,1,"")')><FONT FACE=MS P???? COLOR=#000000></FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>6</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=MS P???? COLOR=#000000>12</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>7</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=5><U>Sheet1</U></TD></TR></TABLE>
<FONT COLOR=#339966>You can see the formula of cells only click each above hyperlinks</FONT>

<FONT COLOR=blue SIZE=1>The above image was automatically written by excel VBA. </FONT><FONT COLOR=blue SIZE=1>If you want this code, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and I'll email the file to you.</FONT>
</CENTER>

This is intended to convince you that COUNT really ignores blanks.

A2 and A5 are blanks, generated using an IF formula.
 
Upvote 0
I think I see my problem now.

My if statement: =SE(H14="sales";U14;"") is returning - in the cell for "".

Is this a formatting problem??

Thanks again.
 
Upvote 0
my example works

<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=6><FONT COLOR=WHITE>Microsoft Excel - HtmlMaker.xls</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=6>File(<U>F</U>) Edit(<U>E</U>) View(<U>V</U>) Insert(<U>I</U>) Tool(<U>T</U>) Data(<U>D</U>) Window(<U>W</U>) Help(<U>H</U>)</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>A1</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=3 BGCOLOR=White></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>E</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>sales</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('12345')><FONT FACE=Arial COLOR=#000000>=IF(B3="sales",C11,"")</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>no sales</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('')><FONT FACE=Arial COLOR=#000000>=IF(B4="sales",C12,"")</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>6</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>7</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>8</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>9</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>10</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>11</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>12345</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>12</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=6><U>Sheet1</U></TD></TR></TABLE>
<FONT COLOR=#339966>You can see the value of cells only click each above hyperlinks</FONT></CENTER>
 
Upvote 0
On 2002-05-10 15:12, ant wrote:
I think I see my problem now.

My if statement: =SE(H14="sales";U14;"") is returning - in the cell for "".

Is this a formatting problem??

Thanks again.
Click to expand...

What is the value in U14?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
If(vlookup or formula
Replies
2
Views
151
Jon3sy
J
N
  • Question
Count a value in a cell (within a specified range) every nth cell along the row
2
Replies
17
Views
506
KRice
K
L
  • Question
Numbers as text
Replies
4
Views
413
Jeffrey Mahoney
Jeffrey Mahoney
G
  • Solved
Help needed in one of excel problem.
Replies
6
Views
133
MARK858
MARK858
T
  • Question
How to *generate* data for a waterfall chart (not the chart itself)
Replies
0
Views
78
trippknightly
T

Forum statistics

Threads
1,214,592
Messages
6,120,433
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top