# Counting From Various Columns

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
ADVERTISEMENT
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
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
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
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
Dec 19, 2012

What I am trying to achieve using the example below:

ID Subject 1 Subject 2 Subject 3

1 Italian French German

2 Italian Art Physics

3 German French Italian

4 French Italian German

the result:

Italian French German 3

Italian Art Physics 1

As in the example, the combinations of Italian, French and German where counted, irrelevant of whether the subjects are in 2nd, 3rd or 4th column.

I tried to do this task by creating a pivot table but there are so many permutations and subjects that it would take me a long time to add the combinations.

View 6 Replies
View Related
May 17, 2014

BP Formula.xlsxI'm not sure what formula to use or if Excel can do this. I want to use it to determine number of blood pressures in a certain range. So if I use column A for SBP (systolic BP) and column B for DBP (diastolic BP). I want a formula to tell me if column A is between 140-159 or column B is between 90-99. I can get that part, but what I'm having trouble with is it counting each row twice if both column A and B meet the criteria, whereas I want every row (person) to only be counted once if either column A OR column B meet the criteria. (See attachment)

View 5 Replies
View Related
Dec 5, 2013

I have data like so

A B

water juice

water water

juice soda

water juice

juice juice

I need to count the number of occurrences when the answers between column A and B are not matching.

View 3 Replies
View Related
Mar 10, 2009

I'm on Excel 2003 and have a sheet to track people who have read my emails. I want to know the answers to the questions below. I've included a fake sample to show what I'm looking at. The number of cells in each column is much higher and not the same per column. I've tried variations of MATCH, VLOOKUP, SUMPRODUCT, etc. but haven't found a good way to set up a formula.....

View 6 Replies
View Related
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
Jun 17, 2014

Fixed data labels over a number of columns and i want to count the number of times 2 of these appear however when ive tried to use the countifs function it will only count if in the first colum of the range selected and for example if A2 says 'FRANCE' and D1 'RED'

Ie i have been using COUNTIFS('Datasheet'!BB:BE,A1,'datasheet'!BE:BH,D1)

View 3 Replies
View Related
Nov 20, 2013

I have a spreadsheet with over 15 columns showing drivers (names numbers etc) and their duties with more inf.

What I need is to be able to find when a certain type of driver in column A (drivers belong to different rutes) say from route A is at rest (this is shown as RD) which is shown in column B and then be able to count how many times those two exact events occur.

View 1 Replies
View Related
Mar 19, 2007

From a base table, I need to create a new table listing only the unique combination of 2 columns and their individual sum. Example:

Base Table:

ITEM-----Loc-----QTY

Pen------School----5

Pencil----Office-----9

Binder---Office-----9

Pen------Office-----6

Pen------School---15

Binder---School---12

Pencil----Office----10

Binder---School----6

Pen------Office----11

I need to have a table resulting to the following:

Item-----Loc------Qty

Pen------School----20

Pencil----Office-----19

Binder---Office------9

Pen------Office-----17

Binder---School----18

View 9 Replies
View Related
Nov 19, 2007

I have a fairly large spread sheet where I need to count the number of instances where the number in one column matches the number adjacent to it in a second column.

View 9 Replies
View Related
Feb 16, 2005

I have two columns of data: 1 column can be possibly anything, either numeric, alphanumeric, or alpha only (VINS or vehicle numbers). The adjacent column are four different types of vehicles. I've tried concatentating the two to try and count the unique combinations (a truck maybe used more than once during the day), which I can get unique values, but not unique values for each type. Also, the range sizes are variable, but no more that 65 rows.

An example:

5CZ975 Van

25RW04 Straight

5CZ975 Van

ZJ6026 Van

So, the result I am after is 2 vans, 1 straight truck

I found an array in a another post, but it is not working properly (see cell F7 in the attached):

=sum(if(frequency(if($b$2:$b$18=$f7,$a$2:$a$18),$a$2:$a$18)<>"",1,0))

**ctrl+shift+enter**

View 5 Replies
View Related
Sep 12, 2006

I have a data set with rows of data, with a header row along the top. I want to sort my rows of data by column when the cell above the appropriate column is pressed. I know this seems cumbersome - but it fits in with the sheet. The sheet is protected, and we only want the user to be able to sort in this fashion. My problem is, with my code, I get an error when you select a range of two of the header cells. The case selection on the target.column gets confused. How can I check to see if it's just one cell? Is there a property of a range that reflects how many rows or columns it contains? I tried the following (marked with the ' )just from picking out of the pop-up list of properties of the Target range in SelectionChange, but obviously it doesn't work. My original code is the un-'-ed code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' If Target.Rows = 1 And Target.Columns = 1 Then

If Target.Row = SortRow Then

Select Case Target.Column

Case PriorityColumn

SortData PriorityColumn

Case JobColumn

SortData JobColumn

Case NotesColumn

SortData NotesColumn

Case SupervisorColumn

SortData SupervisorColumn

Case ShutdownColumn

SortData ShutdownColumn............................

View 2 Replies
View Related
Feb 1, 2007

I have a sheet with data which is all sorted by columns.

What I want to be able to count the occurrence of an instance when 2 cells in the same row (but in different columns) match my given criteria.

Ive been using 'countif' to count the instances in a single column and I am now aware that this is about all it can be used for.

An example of my data is:

A B

1 a

1 a

1 c

2 d

2 c

3 b

3 b

3 c

3 d

What I want to be able to count for example is how many times '3' & 'b' occur on the same row (in this case the answer would be 2).

Im not very good with pivot tables and have tried but have not managed to find an answer. I really hope that someone can help as Ive been working on this probelm for days and quite frankly its doing my head in :-)

View 9 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