Canadian Mac Forums at ehMac banner
1 - 15 of 15 Posts

·
Registered
Joined
·
278 Posts
Discussion Starter · #1 ·
I am trying to count the number of non '-' entries in a column. All entries are either a 6-digit 'Text' number or a dash. I can get the number of entries that are not blank (CountA) but want to add a dash in some cells, but not include those cells with the dash in the count.

Is there a way to do this? I am stuck at the moment.


TIA
 

·
Premium Member
Joined
·
31,013 Posts
Excel is a pain for this - is there just one column or multiple?

Basically you need to define the column then sort it. That will give you continuous - - just look at the cell numbers.
 

·
Premium Member
Joined
·
3,899 Posts
Do a calculation in another column with whatever test you want to count for

=IF(A2="-",1,0)

Run that calculation down to the end of your data
then sum that column for the total

Advantage of this is it will continue to count as the data is changed.
 

·
Tritium Glow
Joined
·
7,141 Posts
how bout this:

=SUM(COUNTBLANK(A1:A20)+INT(COUNTIF(A1:A20,"-")))

This gives you blank cells and " - " , then subtract this from all cells.

Why are your numbers formated as text?
 

·
Registered
Joined
·
278 Posts
Discussion Starter · #5 ·
I tried the solutions, but all they ended up giving me problems. The numbers are formatted as text because they are 6 digit date & time entries. ie. 041450 is the 4th day of the month & the time.


CanadaRAM - your solution was the most promising. I created two columns (A,B) and hid them. The only problem was that the cell with the formula for adding the columns up did not reflect newly inserted rows. So, on adding new rows, the formula didn't recognize the extra rows.

kps: the cells are either dashed or have text in them. I couldn't find something like COUNTNONBLANK instead. A column with 19 entries & four blanks just gave me the four blanks. It would be nice if there was a count if characters in the cell are >1!

The problem lies in that it is an inconsistent number of rows - AND - the two people I have in the office are pretty much illiterate when it comes to computers. They can do something if it is easy. Even inserting rows so that the present formula increases to count new rows (right click row number & select Insert) is difficult to get across! I leave here in two months, so it needs to be as easy as possible.

If they can't get it to work, they'll just have to manually add the entries until the next guy arrives who is computer-savvy!
 

·
Registered
Joined
·
278 Posts
Discussion Starter · #6 ·
:( After googling around all morning, it seems to be impossible to do this with text.

I have changed it to general number. End users will have to put up without the leading zero for the date. At least now when I insert a row, the formula updates with the added row. Why it would not with CanadaRAM's solution is beyond me.

Microsoft huh - and why is it copy & paste is the same as in a pc? That is so annoying! Why can't something copied stay in memory (like a Mac) when the wanted data is no longer selected?
 

·
Indigent Academic
Joined
·
8,115 Posts
Sounds to me that what the situation calls for is a database (reccommend Filemaker for simplicity unless you are an SQL wizard) rather than a spreadsheet. Inputting date as a number formatted as yyyymmdd can be very handy when it comes fo sorts and finds.
 

·
Registered
Joined
·
278 Posts
Discussion Starter · #8 ·
Sounds to me that what the situation calls for is a database (reccommend Filemaker for simplicity unless you are an SQL wizard) rather than a spreadsheet. Inputting date as a number formatted as yyyymmdd can be very handy when it comes fo sorts and finds.

Thanks - unfortunately, all the government uses is Access which is a pig to learn. All end-users want a spreadsheet, which is simple to open & print or extract data from.

A database simpler to use than Access would be great. I did try to get Access to keep track of everything - as what we do is better suited for a database, but it is too complicated. If the military would use simple app's - like MS Works, which would suit 90% of all users, life would be so much better!
 

·
Tritium Glow
Joined
·
7,141 Posts
kps: the cells are either dashed or have text in them. I couldn't find something like COUNTNONBLANK instead. A column with 19 entries & four blanks just gave me the four blanks. It would be nice if there was a count if characters in the cell are >1!
COUNT will count number entries only, that's why I asked why you had them formated as text. In other words, COUNT would do what you asked with 'COUNTNONBLANK'. The dash is text and would not be counted along with the empty cells.
 

·
Premium Member
Joined
·
31,013 Posts
Maz - if it worked but just need new rows accommodated - just add a very large span

1:1000 to your formula to cover off additional rows.
 

·
Premium Member
Joined
·
3,899 Posts
CanadaRAM - your solution was the most promising. I created two columns (A,B) and hid them. The only problem was that the cell with the formula for adding the columns up did not reflect newly inserted rows. So, on adding new rows, the formula didn't recognize the extra rows.
IIRC if you insert rows WITHIN the existing range of the SUM, they will be counted. If you add rows to the bottom of the column, OUTside the existing range, the range will not expand. MacDoc's solution is correct, and I forgot to remind you in the original post -- make your range larger than it has to be.
 

·
Registered
Joined
·
278 Posts
Discussion Starter · #12 · (Edited)
COUNT will count number entries only, that's why I asked why you had them formated as text. In other words, COUNT would do what you asked with 'COUNTNONBLANK'. The dash is text and would not be counted along with the empty cells.

Would be good but it looks like Excel 2003 doesn't have COUNTNONBLANK in its repertoire!

I did check and having the numbers formatted as Number, the COUNT(range) did omit the dash. So the powers that be will have to be happy with 5 or 6 digits for the date-time group from now on I suppose.

Government huh?
 

·
Registered
Joined
·
278 Posts
Discussion Starter · #13 ·
IIRC if you insert rows WITHIN the existing range of the SUM, they will be counted. If you add rows to the bottom of the column, OUTside the existing range, the range will not expand. MacDoc's solution is correct, and I forgot to remind you in the original post -- make your range larger than it has to be.

I did the insertion within the range & at the end, but the formula wasn't incremented. Excel 2003 seems to be lacking in other areas also.

I'd make a larger range to cover all, but there is a summary at the bottom of the rows with data. Which is why I tried Access - but even the Access expert up here (6 years ago) had trouble with the relatively simple (IMO) database I was attempting to make. Maybe I will attempt it again, but Access is so complex, even for the most basic of databases one needs.

I leave here in two months - I just wanted to try to get something simple going so there is at least some consistency with the reports.
 

·
Premium Member
Joined
·
31,013 Posts
The summary can be moved down then split the workspace to only show the summary in the bottom screen.
Insert as many rows as you need between the two again there is little limit.
There is no need to collapse the rows that are empty as they will be hidden under the lower pane where the summary is.



Both titles at the top and the summary can be frozen with only the rows in between scrollable.

Here's specific info if you are not used to panes...it's one real benefit in excel.
http://www.bettersolutions.com/excel/EDO113/NE215138332.htm
 

·
Registered
Joined
·
278 Posts
Discussion Starter · #15 ·
The summary can be moved down then split the workspace to only show the summary in the bottom screen.
Insert as many rows as you need between the two again there is little limit.
There is no need to collapse the rows that are empty as they will be hidden under the lower pane where the summary is.

Both titles at the top and the summary can be frozen with only the rows in between scrollable.

Here's specific info if you are not used to panes...it's one real benefit in excel.

Oh ya - the pane is split anyway what with the number of entries and the need to know what each column is for. It's just that these two guys are complete dweebs with computers. I have to take them by the hand and tell them to double-click (not open - one asked how to open?!!!!) files. They can't do simple editing. To think that handovers are usually accomplished in less than one week - here they have me for 3 months. Two more to go - I won't survive! I am just trying to make it easy for them. :rolleyes:
 
1 - 15 of 15 Posts
Top