# Counting Number Of Used Columns?

Oct 19, 2012
I want to have the number of used columns in a worksheet. The information will be used to understand what range the data is in. So for the rows I am using nRows = Application.WorksheetFunction.CountA(myRange). I am hoping to have something similar for the columns.

View 6 Replies
ADVERTISEMENT
Dec 19, 2012

I have this data lets say id denotes a particular house and the multiple entries of the same house are for different members of a house. Now i want to calculate the total no of people who earn in a particular household i.e if a cell is filled or not.

idB C

00118000040000

001

001

001

002170000

[code].....

View 2 Replies
View Related
Mar 3, 2007

In my Macro , i add a worksheet and depending on certain conditions i decide whether to populate it or not.

At a later point of time i am checking if the newly created worksheet is empty or not using the following check :

If ActiveWorkbook.Worksheets(ISheet).UsedRange.Rows.count <> 0 Then

Now what i have noticed is that even in cases where the "ISheet" is empty the above condition evaluates to true for the first attempt. I cannot understand why ?

let me know if the query is ambiguous and i will post the code snippet

View 3 Replies
View Related
May 16, 2014

I've been desperately trying to figure out a formula that allows me to count the number of rows in a column that have different values, but that only counts those rows in which other conditions have been met. Probably sounds a little confusing, which is why I'll illustrate it with a screenshot:

The file contains a listing of major executives of a variety of different firms in different years from 1992 onwards. In the screenshot for example, you see the executives of AAR Corp from 1992 to 1997. Since my file however consists of 240,000 rows, there's a number of c. 2400 firms with their executives over a time period of 20 years each. Since not necessarily all executives remain with their companies for the rest of their lives, some executives show up for a number of different firms in my sample.

I want to determine at how many firms each executive worked within his current industry up to the respective year, based on the information of the 2400 companies and executive data over 20 years. The current industry is classified by 1 digit SIC codes (column U) and each firm has an individual firm identifier which is the CUSIP in column S.

For example, when applying this to the first row in the screenshot (Ira Eichner), I would need a formula that counts the number of different CUSIPs (thus firms), given that the EXECID (executive ID, identifier for each executive; column I) equals 9248, the 1 digit SIC code (column U) equals 5, and the Year (column J) is below 1992.

View 7 Replies
View Related
Mar 6, 2003

I have several columns in an excel sheet which contain values (eg. Names). The entered names can be unique or already exist in the column. I need to find out (using a function or macro) how many different names were entered. Duplicates shshould be ignored in the count.

View 9 Replies
View Related
Feb 7, 2014

If I have 1,000 entries in a column is there an easy way to determine the number of times a certain item is in the columns. For example if field A1 is "qwerty" I wan to see how many times "qwerty" or field A1 appears in the entire column?

View 2 Replies
View Related
Aug 21, 2013

I have a range of data and need to summarize some of the data. In the table below is an example of the data. I would like to have a single formula where I can count the number of rows where the end date - start date is < 14 days. I would prefer not to add an additional column for the datediff value. Is there a way to do this with either arrays or named ranges?

Name

Start

End

Other data

Verizon

1/10/2010

1/25/2010

AT&T

3/2/2010

4/14/2010

View 1 Replies
View Related
May 23, 2014

I need a formula that will look into a range of data and tell me whan the last time a value exceeded 0 (working backwards).

So below the first row would return a value of 6, the next 5, the next 0, the next 1 and so on....

I can do it with an if formula but the amount of days it will be looking at will be too many, plus the range will keep growing as time passes.

FriSatSunMonTueWedThuFriSat

222000000

111100000

111100011

110111110

000111111

000000011

111111111

111111111

5117400000

564000000

8110660000

0000018171318

View 3 Replies
View Related
Oct 13, 2008

I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.

ID 1ID 2

12342122

45671112

89101718

11122678

13144544

15162324

17189987

19201215

21221928

1976

2576

2345

4678

In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.

View 4 Replies
View Related
Nov 22, 2007

I've tried everything I know (which isnt that much to be honest. lol). Ive tried the frequency formula but that doesn't work the way I want it - I think its probably the wrong formula to use. I've also tried a pivot table but they always vex me. If a pivot table IS the way to go, could someone talk me through it step by step? (*the wizard is just as confusing as doing it yourself I find) ....

View 9 Replies
View Related
Jul 10, 2014

I have a spreadsheet for tracking jobs. Most everything is based off of week # rather than date. I am trying to get the stats page of the workbook to tally the total number of late jobs per week.The current week is taken care of because there is a function that automatically displays on time yes or no and I just set it to count the yeses or nos.The problem I am having is for past weeks.

I tried- =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<Weeknum(Now())" but that doesn't work. I also tried isolating the < like this. =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<"Weeknum(Now()) and that did not work either.

------ UPDATE

In response to using CountIFS I have also tried-

=COUNTIFS(Table2[On-time],"NO",Table2[Due Week '# Hidden],"<Weeknum(Now())") this just returns a zero value even when I have a late job listed three weeks ago.

View 10 Replies
View Related
Jan 5, 2007

I need to know wich formula or VB method, I have to use to count the number of cases in a range of colomns and rows C4:G107, have the same number. For eaxample how many times do I have number 1 and so on...

View 9 Replies
View Related
May 23, 2006

I have a column for EE or SP and a column that has an amount of insurance

coverage like 10,000 etc.

I want to count the total number of EE have that level or type of coverage

My columns are like this:

C1 EE K1 10,000

CE SP K2 200,00

I have tried numerous formulas from the various discussions and I can't find

any that works.

View 11 Replies
View Related
Dec 23, 2006

How do I create a formula that will count the number of times in F7:H106,

where the number "0" occurred and had a value of "1" to occur

in the previous row in the range, if no number "1" in previous row

then ""

examples

if G22=0 and G21=1, then count 1

if G12=0 and G11=1, then count 1 more

View 9 Replies
View Related
Apr 29, 2014

I have a spreadsheet with 3 sheets, the first sheet is to be an overview of the entire workbook used to keep track of training,

The last sheet (Called Raw_Data) contains various coloumns, the two I am interested in is the training course and the name, as someone completes a course I record what the course was, who did it and date/time of start/finish,

What I would like to do it to count on the front sheet how many times someone has completed a course

Operational Overview

Joe Blogs

Work Pratices

Joe Blogs

Incident Handling Processes

Joe Blogs

Training

Paul Smith

Incident Handling Processes

Joe Blogs

From the above table Job blogs should flag in a cell for Incident Handling Processes twice, Is this something that can be done via a formula as I am trying to avoid VBA.

View 1 Replies
View Related
May 29, 2006

This is a very simple thing that i can't seem to figure out or find the right code in search.

How do i count the number of columns in an excel worksheet?

- No selection should be made

- If it helps, the worksheet only shows the columns and rows with data, if there are no data there are no columns, something about the Page break view...

View 9 Replies
View Related
Jun 27, 2014

I am trying to count how many times a certain value appears in a column. For example how mnay times A81001 appears in column A and compare with column B. I suppose it's Count function?

column A column B

A81001A81001

A81001A81001

A81002A81002

A81002A81002

A81002A81002

A81002A81002

A81002A81002

A81003A81003

A81003A81003

A81003A81004

A81004A81004

A81004A81004

A81004A81004

A81004A81005

View 12 Replies
View Related
Mar 17, 2009

In my table I have a column called Series and Column called Games.

Now when a user makes an entry the do the following

Enter the Date of a game

then a Series # (I wish I could make this auto increment because a series number can never be used more than once.)

then a game number starting with 1 and never going past 3.

Then Bet Team, Home or Away and Vs Team.

Now let it be know if the column called w/L = Win then that Series is over for ever and there will be no more games played.

If there W/L = Loss then I must create a new row for that SERIES with the next game number.

What I was hoping to do is

If I write in the following going in order

Date ---4-11-09

Series # ---- 6

It should auto populate

Game to the next game #

Bet Team

H/A

Vs Team

I also want it to update my record

C3 is my series Wins

D3 is my series Losses

I do not know how to populate that.

A Series Win = during a series game 1-3 where W/L = Win

A Series Loss = a series 1-3 where there are no wins. A series does not count as a loss until you loose game #3.

View 10 Replies
View Related
Jan 9, 2014

I need to get the count of a string from multiple columns. The problem is that string1 exists in column A and String2 can exist in one of eight columns. See the example below.

What I want to do is use two search criteria. I want to find the number of times I have Type=A and if the row has a case Beta. In the example above I should get an answer of 2. (From rows 3 and 9)

I tried countifs but I cant seem to get it working properly.

Attachment 288701

View 3 Replies
View Related
Jul 25, 2012

I have two columns that represent constituents from a stock indices from two different years. I would like to find out what stocks appear in both years.

View 1 Replies
View Related
Sep 10, 2013

How can I count all the cell text from different columns?

I have a sheet..that has the well known columns from A to EM, but let's say A to Z (A,B,C,etc).

I want to count all the cells that have text inside from columns: A,C,E,G,I (so, from 2 to 2). But the counting to be for all these A,C,E,G,I, etc columns, and to be put in a cell which gives this total number as info for user.

How can I do this?

View 9 Replies
View Related
May 16, 2014

I Have a column in excel that contains different words like true false single double wide. I need to count in total how many say true and false.

True

False

Single

Double

Wide

True

True

The answer i need is 4

View 2 Replies
View Related
Dec 11, 2013

I was trying to see the values only if the same value arrived more than three times in the given range.

Ex:

ABCDEFGHIJ

1AAAABAA

2

in the given range A1:J1, I would like to count 'A' only if it repeats more than or equal to 3 times. For row 1 I should get count as 4.

View 1 Replies
View Related
Oct 17, 2013

So, in general I do have two columns F and G taken from the other xls.

Age is obviously difference between today and open date

Open date is open date.

I made a table like:

Age and due date.png

In this case, I have 3 rows where there is no open date extracted, therefore is no age. The counter stops on them and shows 529 in total instead of 532 or shows the age as far more than 365 days. How can I count the blank cells, but only in the range of the list I do have, not the all blanks I have from the beginning till the end of the column, so I could (for similar in this case) have 3 blanks cells counted?

Sometimes is also stuck in the middle of counting (when blanks are inside there) and the total number is even smaller. What function can I use to count these 3 (or less, more inn the future) as BLANK to have the total numbers realistic?

View 6 Replies
View Related
Nov 23, 2011

=SUM(IF(FREQUENCY(IF(B2:B20="Temp",MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(A2)+1),1))

The works when referencing two columns. I need a total count of unique across three columns.

Sample Data

A | B |C

Jason | Temp | 1

Jason | Temp | 1

Jason | Final | 1

Kim | Temp | 1

Mary | Temp | 2

My summary table has the unique values of C in first column and the unique values of B as headers. I want the unique counts as the data.

Temp | Final

1 | 2 | 1

2 | 1 | 0

View 5 Replies
View Related
Sep 30, 2013

If I have two columns of data, and I want to count the numbers of times a certain letter appears in column A and another certain value appears in column B, I use the following formula:

=sumproduct((AA="yes")*(BB:="X"))

What formula would I use to count the number of times "yes" appears in column A, AND EITHER "X", "Y", or "Z" appear in column B?

Would this still be a SUMPRODUCT formula, or would a cOUNTIF formula be used?

How about counting the times certain letters appear in THREE columns?

View 2 Replies
View Related
Jul 25, 2008

I am using Excel 2007 on Windows XP. I have written up a questionnaire that allows the user to simply check boxes in the cells. My problem is coming up with a formula to count the number of boxes have been checked. I tried several count formulas without any luck. I think one of the problems is that when I highlight the cells with these check boxes it doesn't actually show in data in the fx line.

View 9 Replies
View Related
Apr 22, 2005

I have an Excel document that I want to count the number of rows that meet a text value in two columns.

Column B has a value of either "Open" or "Closed".

Column C has a value of either "High", "Med", or "Low".

I want to count the number of rows that are "Open" "High", "Open" "Med", and "Open" "Low".

I've been studying a number of different formulas, but I can't seem to get my head wrapped around this functionality.

View 9 Replies
View Related
Mar 24, 2014

My data is as follows:

Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Number of Starts 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10

Rolling Number of Cases 10 20 30 40 50 60 70 80 90 100 110 120 120 120 120 120 120

In my dataset above the cases finish after 12 months (hence why there is a maximum number of cases).

What I am seeking is a method to count along a certain number of cells, then sum the contents of those. For example, I would like to count the contents of up to 12 cells, then sum those 12 cells.

Where it gets difficult is as follows:

- In month 6, I am just counting the contents of 6 cells (months 1-6)

- In month 12, I am counting the contents of 12 cells (months 1 -12)

- In month 18, I am counting the contents of 12 cells (month 7 - 18)

View 5 Replies
View Related
Mar 27, 2014

I need a formula, from which I need to drag (if needed) and to fill with numbers, by following principles:

- when the left cell contain the zero, it needs to find on the same column if there exists another zero, or another one and count them, let's say if find three zeros untill find number one, return 3.

- when find a number 1, after another 1, has to return a zero.

- the formula need to initialise from the top of the column, only finds the first number 1.

I have a relevant example down bellow. I used to count them visually and sometimes errors sneak into my spreadsheets.

View 5 Replies
View Related