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.

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.

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 ....

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.

The formula has to include a range across each row (for each student). The formula has to search for specific text contained in the classnames, and then return the result along the same row.

Below is an example, which I hope saves OK in the thread. If not I'll upload an Excel file:

A B C D Results:- 1 class1 class2 class3 class4 Gg Hi Fr Sp 2 10m/Gg1 10m/Hi2 10m/Fr1 10m/En1 TRUE TRUE TRUE FALSE 3 10n/Hi1 10n/En2 10n/Sp1 10n/Ma1 FALSE TRUE FALSE TRUE

I am trying to count the instances of a certain text in a range across 2 columns.

so lets say I have the word Dog in A1, A2, A3, A4 & B1, B2, B3, B4. I am look for the number beside each cell to say 1, 2, 3, 4 instead of summing the whole range.

I have tried the array formula of {=SUM(IF(C:D=C2,1,0))}. However this does just bring back the sum of the instances and I am clueles on how to get it to show the first instance, the second etc.

I try to write the function that gets some "ref" and returns appropriate item. For example: if I give to function "C3", it returns "AAA" if I give to function "R18", it returns "BBB" (cause it between R15 to R26) if I give to function "R9", it returns "BBB" also.

I'm trying to create an Excel function that allows me to choose a row from a table based on text found within entered data. For example, say I have a table called "categories" that looks like this:

Categories cat dog mouse

Now in a different section of the workbook, I want to be able to enter an arbitrary number of sentences, one per row, that look something like: "It's raining cats." "The mouse is hiding."....................

I am not even sure if what I need is a possibility with excel. I need to "find" specific information and the amount of time that is attributed to it. All of the information is provided via drop down box (times as well) in daily segments then grouped by two week (bi-monthly) chunks and have the total times for each information type placed elsewhere. There are notes placed in the excel file to the right of the information you will see when first opening the file.

I import them into a excel worksheet, but the problem is with selecting them, because the data are really messed up. I tried selecting them with sumproduct with criteria, but the problem is that the formula wants the criteria to be a text that is in only one cell, whereas in my case there I would like the formula to select for example the cell that has the text "Visa34" as well as the cell with "Visa12,FIB3" (i.e. all the cells with "Visa" even thought there might be other words or letters in the same cell...).

I have a column of data with letters in each cell, no numerical, only alpha. Now, some of those cells contain the letters "adj sub" as part of the text string in each cell. "Adj sub" is always at the beginning of the text string. As an example, a cell will look like this - "adj sub mhm". I want to delete rows whose cell description does not contain "adj sub" as part of the text in the cell.

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.

Also I have a Userform with 2 ComboBoxes named "ComboBox1" and "ComboBox2". Values in ComboBox1 is "Pull my pants", "Eat my shorts", "Socks for everyone".

What I would like to do is to search though column A and look for any of these textstrings in my selection in ComboBox1 and return the value from column B in column C.

Example: "Pull my pants" is selected in ComboBox1 then the value "pants" should be found in column A and value in column B (25) should be entered in cell C2.

I am fairly new to VBA and have spend hours searching Google and found some formulas like InStr and VLookup. My problem is that I am not sure if these statements will do the job and how to combine them.

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

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'"

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.

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.

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,

I have been working on this worksheet part of which is attached herewith. I would like excel to automatically enter Updated/Inserted in column B against Individuals' names as per the instructions given in column A. For example: As per instruction in A9, B13:B16 should show Updated. I have tried to use the nested if function, but it does not work as I want it to. Also as I am not used to macros or VBA codes, could this problem be solved with formulas?

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 ....

Is there any way to remove the first part of a string of text in a cell and save the second part?

The first part of the text string is a team code that has a variable number of numbers, capital letters and sometimes spaces. The second part of the text string is a variable number of words in a team name that all start with a capital letter and have lower case letters. Every line has a different team code and team name.

The original spreadsheet also has a column with just team code. Is there a way of using this column to "subtract" the team code from the text string to just leave the team name?