Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Counting number of different occurrences of data in a column

I am using numbers as a way of referencing data to a category.

e.g. below, Alison, Emma and Peter all belong to one family, with the code 1.

Parent FChild MChild Family code
Alison________________1
_____ Emma__________1
____________Peter____1
David
_____Elena___________2
_____Lisa____________2

I need to count the number of families, and therefore wondered if there was a function to count the number of different values in a column, so in this case the result would be two because there are two different values.

many thanks,

maharg


Post your answer or comment

comments powered by Disqus
how to get the number of rows with data in a particular column.

Date

12/2
11/3
10/2
11/4

here DATE is the column heading

and here number of rows with data is 4

HOW to get it from VBA statements

I need a code to count how many cells have data (0.00) in column - A, then in the column - B paste the number '40' down to match the number of cells in column - A.
Also I need to add up all the data and have the total in the next free cell in column - A, and the number '50' in the matching cell in column - B.

Every time the report is run the number of cells used could/will change.

I have attached a example of what I need to end up with.......

How do you count number of characters(tabular data) in a single cell?

Counting rows.xlsx

What formula can I put in Col B?

So that it counts the number of times the item in Col A has appeared.

Data cannot be sorted and preferably not a CPU intensive formula as its going to be put into a 15,000 row spreadsheet

Thanks

I have a file........see enclosed.

I want to count the number of non-blank cells in a range from A to Z along a given row.

Then once the loop is done i want it to tell me the row which had the most non-empty cells but its not working.

Have in column C values, some of which end in =T.
I have data in columns D through H some values(numbers).
I'd like to do two things:
Obtain the number of cells that are in each column that have a
corresponding value that ends with a =T in column C.
Also, obtain the total of the values of the cells in each column, also
of which have a corresponding value that ends =T in column C

Tried messing with sumproduct, but haven't hit on the right formula.

TIA for thoughts.
Pierre

Dear All,

Hope all fine

Suppose i have a range A1:A13 and it is filled with data. suppose the data is as below.

Fun
Funny
Hunny
Sunny
Fun
Funny
Hunny
Sunny
Fun
Funny

Now i want to count number of times "un" appeared in this column A.

how can i do it using excel formula ?

Hi,

I need to count the number of rows containing data in a worksheet and then hold this data in a string called countrow, very simple but cant quite work it.

Thanks

Anyone know a quick macro that will count the number of entries in a particular column that are located in cell B7 (or just below B6) and then the counted value is stored in a variable called "n"? The number of items listed below B6 can vary.

for example:

B6:
1
2
3
4
5
6

And then n would equal 6

I have a spreadsheet that I am having difficulty sorting. I have a number of usernames with associated information to them contained within their row of data. I am able to sort by username very easily.

What I need though is a way to organize the data in a different manner. I only need the usernames that have been logged multipule times to display, or be separated from those that only appear once.

If I can seperate the usernames that appear two or more times from those that only appear once

I would like to be able to sort by the number of times that the username repeats in the sheet. (i.e) Usernames that have appeard 5 times by alpha at the top followed by 4 ect. This would allow me to eliminate those that only appear once easily and allow me to work with the data I need.

If there is a way to organize the usernames by alpha, but keep those that have not appeared multiple times from displaying, that would be fine as well.

Here is essentially what I have now. The Usernames are sorted by Alpha

Line1: _UsernameA__Info__Info__Info
Line2: _UsernameB__Info__Info__Info
Line3: _UsernameB__Info__Info__Info
Line4: _UsernameC__Info__Info__Info
Line5: _UsernameD__Info__Info__Info
Line6: _UsernameE__Info__Info__Info
Line7: _UsernameE__Info__Info__Info

I need UsernameB and E separated, I can't do it manually, because I have thousands of lines of this, and a low percentage are repeat Usernames.

Ideally it would look either like this:

Line1: _UsernameB__Info__Info__Info
Line2: _UsernameB__Info__Info__Info
Line3: _UsernameE__Info__Info__Info
Line4: _UsernameE__Info__Info__Info

With the other data hidden or Like this.:

Line1: _UsernameB__Info__Info__Info
Line2: _UsernameB__Info__Info__Info
Line3: _UsernameE__Info__Info__Info
Line4: _UsernameE__Info__Info__Info
Line5: _UsernameA__Info__Info__Info
Line6: _UsernameC__Info__Info__Info
Line7: _UsernameD__Info__Info__Info

I know I've seen data organized in this manner before, I just need a way to do it. Any Help is greatly appreciated.

I have searched dozens of messages in this forum and found what is apparently half of the answer to my question. However I need the other half.

I know various ways to find the reference of the cell that has the last piece of valid data in a column, but I don't know how to then include that in a formula. Let me provide an example.

Column A is Date
Column B is a stock price
Column C is another stock price

I want to create a formula that gives me the percent difference between the FIRST date in my data and the last date in the data. I'm currently using:

=B35/B3-1
=C35/C3-1

Then after today's stock prices are entered in the sheet I'll change it to 36, then the next day 37.

How do I use something like the MATCH or ADDRESS command and incorporate it into this formula?

Hi Everyone. I'm trying to figure out how to do a formula for the number of
times 1 happened in a row starting from a certain date.
For example, from A1 I have 1,1,1,0,0,1,1,1,1,0,0 - I would like to get the
answer of 3 (as starting from A1 I have 3 "1s" in a row, the rest of the
numbers aren't relevant). The max I want to be able to get is 11.
--
Thanks!

Stephen

I am looking for a command/formulat to count (return a single number) the
number of unique text entries in a column.

I know this has to be real simple, but I can't find an answer.

I have a column ("A") of data on a sheet ("OldFiles") and I just need to
know how many cells are in that column have data in them. The column is
sorted, so the non-blank cells will be contiguous and will be at the top of
the column.

TIA,
Ken

Hi Everyone. I'm trying to figure out how to do a formula for the number of
times 1 happened in a row starting from a certain date.
For example, from A1 I have 1,1,1,0,0,1,1,1,1,0,0 - I would like to get the
answer of 3 (as starting from A1 I have 3 "1s" in a row, the rest of the
numbers aren't relevant). The max I want to be able to get is 11.
--
Thanks!

Stephen

Hi everyone. Does anyone know of a code or macro that can reverse the order of data in a column? Like for example, if I had a column of data like this:

2
3
6
7

But I would like to reverse the order to:

7
6
3
2

Any help would be much appreciated. I've attached a workbook just in case. Thanks.

i need to calculate number of rows with data in a particular column.
whether the number of rows filled (of column A) with data is 3 or more.
msg hello is printed.
is this syntax correct?

Code:
   

 If workbook2.Sheets("Sheet1").CountA(A) = 3 Then
  Msgbox "Hello"


I want to know what formula to use to find the total number of things in a
column. For example if 'Strawberry' is written in column 'J' 10 times, I want
to have a seperate page that shows that information.

I need to find a formula to return the address of the last cell in a column
that contains data. The location will always change as more data is added to
the sheet.

I am trying to copy a range of data in a column, where not every row has data, and paste it into a column in another sheet, but I only want to paste the rows that have data. please see the attached sample. i want to copy from Range M8:M247 (or from M8 to the last row of data in column M. Please provide code for both.) in Sheet1 and paste just the data rows starting in Cell B2 on Sheet2. this would then have hexidecimal data corresponding to each Cycle in Column A of Sheet2. FYI, the data in Sheet1 cell M7 has already been parsed through and put into columns as headers starting in column B of sheet2, so it is intentional that I started with an empty cell (M8) for the data I wish to copy. Thanks in advance for your help!

Hi All.......

I am trying to set up a macro to graph all data in a column.
The spreadsheet will be used on numerous projects with varying amounts of data. Therefore I need to get the code to select all data in the column, then produce a graph and have this code be flexible enough to work on different amounts of data.

Can anyone help.......please!!!!!

Hi,

I am new to Excel. Please help me with the below problem:
I want to write a script which will count the number of consecutive blank cells in a column. for eg: Assume A1 contains "one" and A5 contains "five". the cells in between A1 and A5 are empty. i want to get a count of the number of blank cells in between A1 and A5, using script. Is there any way i could achieve this?

Regards,
Anu

I am looking for something that will find the last cell of
data in a column, here is what I want to do

the function I have is

=if(a1=0,0,(b1-$cS10)*d1)

C10 is the total for that column

now that works fine on sheet 1 but on sheet 2 and then
sheet the total is in cell C23, due to the nature of the
spreadsheets I am coding it so the formulas get entered
each time by clicking a button. How do I find the last
cell containing data in column C and then use it in the
formula. Once I can find it on one sheet then I can find
it on the rest

Thanks

How can I replace data in a column that is between 1 and 10? This way it will
leave any other text there. I think the whole column is formatted as TEXT so
I may be running into a problem there. Will I still be able to create some
sort of formula

If data >1 or <10 then replace with " "

--
Thanks


No luck finding an answer? You could always try Google.