# Count Instances In Multiple Columns?

Mar 19, 2013
What I'm trying to do is input a formula in col G which will look for instances of the city named in col F in both cols A and C. This should then return the total of these, from cols B and D that have the letter "F", into col H. Therefore, in the attached example, cell G2 would return "1", G3 would be "0" etc.

Should I be using VLOOKUP or COUNTIF, or maybe a combination of these or something totally different?CityCodeCount.xls

View 3 Replies
ADVERTISEMENT
Feb 5, 2008

I have attached a small copy of the worksheet.

I need to count when the following set of 3 criteria criteria are met:

1: The Assessor Initials = DS

2: the month = Mar

3: Check 4 Ranges = columns I, K, M, O

I've tried using sumproduct (sucessful for other data) but not able to get it to work with dates.

View 9 Replies
View Related
Aug 17, 2012

I'm using WinXP with Excel 2003 - I have a column of highway sign description data (16k+ rows).

Example:

Curve Arrow Right

Curve Arrow Left

Turn Arrow

Reversing Curve Arrow Right

Winding Road Arrow(plus many more unique entries)

I'm using SUM and COUNTIF to total the number of times "Curve", "Reversing", "Turn" and "Winding" appear in the column.

My formula is:

Code: =SUM(COUNTIF($F11:$F16196,{"*CURVE*","*REVERSING*","*TURN*","*WINDING*"}))

Which works great EXCEPT what I really want is the number of cells with any of those key words, not the total count of those words. The example above should be 5, but since row four contains more that 1 of the key words I'm getting 6.

View 4 Replies
View Related
Dec 13, 2013

Basically, I have a table with rows as person, and variables as features. If a person meets a feature, the that cell writes 'X', now, I hope to create a master variable that capture all the features, basically counting how many features a person has met. For example, Joe has three features; "have balls," "cool guy", "good with ladies". In the cell in question would be "X" "" "X". And if I count, then it should be 2.

View 1 Replies
View Related
Apr 14, 2009

I need to count instances in a column where the error #N/A is returned. I thought that the following formula would work, but I'm getting #NAME?:

View 2 Replies
View Related
Aug 19, 2012

In a cell i need this info when

column a = month

column o = staff member

column m = discount given

if no discount is given column m will show 100%

i need the total of all sales made with a discount i.e not 100% and not blank, in a certain month, by a certain member of staff

step 2: i need the average of all these for each member of staff shown in a different cell

i already have the total sales counted per staff member so this will show me who is doing deals and who is doing the biggest deals.

View 4 Replies
View Related
Feb 13, 2009

40,000 rows,

Column A is a Port Code . . . always 4 digits

Column B is a 2 digit code representing a mode of of transportation.

I did it the "brute force" way of concatenating the two columns into column C, then sorting and subtotalling column C . . . .

View 9 Replies
View Related
Jul 19, 2013

I have a following Data in One of the Column. I want to count the number of instances.

Ex. There are 3 instances for 120412 , 2 instances for 120471 , 1 instance of 120478 and so on. How to write a formulsa for this?

Configuration: Test ID

120412

120412

120412

120471

120471

120478

128715

128722

128723

128724

128725

129291

129329

8984

8984

View 3 Replies
View Related
Jan 30, 2014

I'm working on a formula for COUNTIF with 3 criteria.

What I'm looking to do is create a formula that counts the number of instances which meet all of the following three criteria:

the value of Column A is >=0 and the value of Column I is >=0 and

View 4 Replies
View Related
Dec 21, 2006

is there a formula that gives a true or false statement upon searching a list (ie A2:A6) and finding multiple entries? like, when i search A2:A6 and there are two fields that contain the word "hello", is there a function to say "true, there are multiple entries of the word 'hello'"

View 2 Replies
View Related
Jan 29, 2007

I need a formula to count the number of times the same thing occurs in a particular column. i.e. tree 1, tree 2, tree 3. I just tried the countif function and it is returning "0". Not sure if that's because the data I am looking for is part number/part text. Does this make a difference? There are also spaces between the number and the text. Also, the range I am asking excel to look in is based on a concatenate formula.

View 4 Replies
View Related
Sep 12, 2007

If you can see the example spreadsheet that has been uploaded, I need VBA to try and count how many times the number 1, 2 or 3 occurs in the column A and give that figure in b15, 16 and 17 respectively. I also need it count the number of times a particular product appears in column B and give those figures in b19, 20 and 21.

View 7 Replies
View Related
Aug 2, 2008

I'm working on a spreadsheet that contains a list of various instances of zip codes between a couple of months. I'd like to count the number of times each particular zip code occurs within each month. And, if possible, if I could get that count to populate into another cell on another sheet within the spreadsheet,

View 7 Replies
View Related
Sep 28, 2012

Want a single count of multiple columns based on the columns selected value. Data is in text format.

Have tried multiple COUNTIF statements and have tried using pivot table (Excel 2010) both only give me total counts for all. I think I need an OR statement somewhere, but not sure where?

In other words, if a single record has an "any" in the any fields or a "yes" in the yes fields, I want to to count that as one record.

Sample data:

Pegnancy Smoke

Pregnancy Alcohol

Pregnancy Marijuana

Pregnancy Powder

Stress Cigarettes

Stress Marijuana

Stress Alcohol

Stress Medication

[Code] .....

View 2 Replies
View Related
Sep 17, 2008

This might be really simple but i don't get it. I have a column with country names (strings). There would be 5 instances of "USA", 10 of "UK", etc, etc.

I made a column next to it, where i want to count the number occurances ....

View 9 Replies
View Related
Jan 28, 2014

I would like to know how to count the instances of Cayenne pepper where the final column value is greater than 0

the result desired would be 2 in this example and the first cayenne record would be omitted from the total as it is a value less than 0

0349598

'M'CAYENNE 48G

6

#N/A

[Code]....

View 4 Replies
View Related
Jul 8, 2009

Is it possible to use VBA to calculate the number of numerical occurance within a column of data, and then categorise the results (onto another sheet) based on a grouping variable found in another column of data? I've been trying to create a macro to do such a thing, but I'm not even close ....

View 9 Replies
View Related
Feb 17, 2014

I have a workbook consisting of two sheets: a Monthly Class Schedule (Divided into five one-week blocks. Each block's vertical is Mon to Fri, the horizontal is 9 class slots. There are 45 class slots pw). Each class has one student and the cells are filled 'Student Number, First Initial, Surname' eg, '666 J Smith'. Students may be scheduled for several class slots per week and some class slots have no students scheduled. When the student attends a 'P'is added at the beginning of the cell eg,'P 666 J Smith'.

The Student Attendance Report has the students listed vertically on the left, arranged by Student Number, and then a column for each week.

At the moment I have to manually count the classes each student has attended for each week and enter the attendance figures in the appropriate week column. It's a PitA so I want to automate the process by using the S#s in the Attendance Sheet to search the week blocks in the Schedule Sheet for 'P S#' and auto-fill the week columns in the Attendance Sheet.

Sched and Attend February 2014.xls

View 1 Replies
View Related
Jan 21, 2008

I need a formula which I am using to count instance of sickness. I include a sample copy of my data which I hope will make my request clearer! I'm currently using the below formula to count the number of continuous instances of any code starting with "SICK" =(SUMPRODUCT((LEFT(C$10:C$37,4)="SICK")*(LEFT(C$10:C$37,4)<>(LEFT(C$10:C$37,4)))))

I would also like this formula to count any code starting with LTS as well (Long Term Sick). Due to the nature of sickness there may be continuous periods where both SICK and LTS codes are used, at the moment I can not come up with a formula to return the value I need. These columns may contain other codes but for simplicities sake I have only shown the relavent ones.

View 2 Replies
View Related
Jun 19, 2014

I'm doing a study of when people are most productful in their day. I just need to count how many units a person made within say 4 hours of starting work.

My data is similar to the excel I've attached

Count completes.xlsx

I'm using Excel 2010 too.

View 4 Replies
View Related
Apr 23, 2013

I need to count how many times a code appears over a 6mth period only on a single day. The data would look something like:-

1/1 2/1 3/1 4/1 5/1 6/1 7/1 8/1 9/1 10/1 etc

a b c a a b a a b b

If counting b the result would be 2 on the above 10 days as it appears on the 9/1 & 10/1 it would not be included. It is to be used to count the number of times somebody only appears on a single day over a period of time but not count them if they come back the next day.

I cant think of a way to do this using in built formulas (Countifs, sumproduct, etc) The example is basic and there could be up to 20 codes.

View 4 Replies
View Related
Feb 25, 2014

I have this work sheet with several formulas in columns Z to AD. All of them highlighted red work fine as for as I can tell. I am stumped with the one needed for the cell highlighted yellow AD2. It should count all the dates in AD1 that are Requested Changes Made and/or Rejected in Column "M". AD2 is a total of today minus 8. Equipment Change out - TEST.xlsx

View 5 Replies
View Related
Mar 13, 2014

What I'm trying to do count the TRUE values in multiple columns, if the criteria is correct in another column.

I've tried countifs but end up having the company included into the count, or only count the row that matches all the criteria.

If I do =COUNTIFS(A2:A7,"A",B2:B7,"TRUE")+COUNTIF(C2:C7,"TRUE") then I get 5.

When I change it to +COUNTIFS(A2:A7,"A",C2:C7,"TRUE") it works but there's a time where I need to check up to 8 Options.

Company

Option 1

Option 2

a

True

True

b

True

False

c

True

False

a

True

False

a

True

False

b

True

True

View 10 Replies
View Related
Aug 17, 2007

I am trying to figure out how to create a formula using multiple criteria in different columns. Ideally, I need to use the whole column (i.e. E:E rather than E2:E400) because I don't want to have to update the formula every time I input data.

I will simplify my spreadsheet for example purpose. Basically, column A has a unique identifier that either begins with an "M" or an "R." Column B either contains a person's name or a "-". Column C contains a dollar amount.

1. I need to be able to count all the cells in Column A that begin with an "M" AND have a "-" in Column B.

2. I need to be able to SUM the $ amounts in Column C ONLY for the items that begin with an "M" in Column A and have a "-" in Column B.

Is there any sort of formula that might do this? I have tried SUM arrays but as I said before, I would rather be able to use the whole column.

View 9 Replies
View Related
Feb 16, 2008

I'm trying to search through a group of Excel files for specific values in column B and then copy and paste the value from column C of the same row into the current worksheet if the search term is found. Thanks to some help from turtle44, I can do that if the search term only appears once, but if it appears more than once, I can only find the first occurance.

I have searched through te archives and found lots of people asking for the same thing. The common suggestion is to use .FindNext, but no matter how I try to use FindNext, it doesn't give me the results I want. It either seems to find the first occurance again or to find nothing at all.

Here is a simplified version of my code, if someone could just explain how to find the second occurance of "Use Code" and past the relevant value into column F of the destination workbook, I'd appreciate it and I should be able to work out the rest on my own.

Sub SearchAllXLSFiles()

Dim lCount As Long

Dim wbSource As Workbook

Dim wbDestiny As Workbook

On Error Resume Next

Set wbDestiny = ThisWorkbook

View 5 Replies
View Related
Jan 14, 2014

I have multiple columns in excel that contains values like this

A B C D E F G H

TrxVolTrxVolTrxVol Trx Count Vol Count

122001400013500

125031290012499

130001300012700

Now at the last columns G & H i want to get the result that how many column of title "Trx" are having values greater than or equal to 1 and how many columns of title "Vol" are having values greater than 2500 respectively.....

View 1 Replies
View Related
Dec 21, 2004

I have a column (A) in sheet1 with these values:

Code

a1 04800128

a2 04800178

a3 04800128

a4 04805555

a5 04800128

And in Sheet2 - Column A and B has these values

Code

a1 04800128

a2 04800128

a3 04805555

a4 04800128

a5 04800128

Status

b1 Y

b2 Y

b3 Y

b4 Y

b5 N

I need to count in sheet1, where the code of sheet1 will be matched with sheet2 code and its status should be equal to "Y" .. I do not want to hard code these values as I have a huge data.

View 4 Replies
View Related
Jan 13, 2013

I have a large dataset with a company name in Column A and a Town in Column B, e.g.

ABC Company London

ABC Company London

XYX Company Swindon

STJ Company Bristol

STJ Company Bristol

STJ Company Bristol

DEF Company London

DEF Company London

DEF Company London

and I need to identify every instance where the same town appears for each instance of each individual company and add in Column C incrememnt the town name, so the above should end up looking like this:

ABC Company London London

ABC Company London London-1

XYX Company Swindon Swindon

STJ Company Bristol Bristol

STJ Company Bristol Bristol-1

STJ Company Bristol Bristol-2

DEF Company London London

DEF Company London London-1

DEF Company London London-2

I'm thinking I need to use some sort of lookup, but because of the volume of the instances of each company I have no idea how many instance appear in the data set. How could I go about achieving this and incrementing the town names in Column C automatically for each instance?

View 6 Replies
View Related
Jan 15, 2010

I want to be able to look at two columns and set a criteria to count. I want to look at column A and if its blank then look at column B and if it has a value of more than 0 then count.

A B

1 1.00

2 Yes 4.78

3

4 5.00

5 Yes 4.89

6 11.99

So this example would count 3

View 3 Replies
View Related
Nov 7, 2005

I need a formula or function that will sum the number of occurences a

specific text string is entered into cells in column B. I only want to count

the occurences when a number that is in column A (on the same row as the

text) is equal to a specific number. Sounds easy but it's very frustrating.

View 9 Replies
View Related