Free Microsoft Excel 2013 Quick Reference

Hi all!

Its there a way to count accuracies of a character in a string using "in cell" formula?

I want to avoid using VBA (it is so requested) and/or "Data"->"Text to Columns" option (I don’t know how to automate it for new entries without VBA, and don’t really have enough space).

=IF(NOT(ISERROR(FIND(" ";M2;1)));IF(NOT(ISERROR(FIND(" ";M2; FIND(" ";M2;FIND(" ";M2;1)+1))));"…";1);0)

This isn't an option do to limited nesting, and length limit. There should be up to 50 occurrences per entry.

Fare as I can tell there is no way to do this, but I'd like check first.

Thanks!

Its there a way to count accuracies of a character in a string using "in cell" formula?

I want to avoid using VBA (it is so requested) and/or "Data"->"Text to Columns" option (I don’t know how to automate it for new entries without VBA, and don’t really have enough space).

=IF(NOT(ISERROR(FIND(" ";M2;1)));IF(NOT(ISERROR(FIND(" ";M2; FIND(" ";M2;FIND(" ";M2;1)+1))));"…";1);0)

This isn't an option do to limited nesting, and length limit. There should be up to 50 occurrences per entry.

Fare as I can tell there is no way to do this, but I'd like check first.

Thanks!

- Find the last occurrence of a character in a string
- Formula to Count Occurences of a Word in a Sentence Within a Cell
- Count number of specific days in month excluding holidays
- How to count occurence of multiple characters in a cell
- Formula to count MAX of a column with condition
- Formula to copy parts of an address in cells and paste in specific columns. - See att
- Formula to count occurrences of multiple elements within a string
- Function for finding position of numeric character in a string
- Function for finding position of numeric character in a string
- Need Help: how to count occurrences of a text character in a range
- Formula to count number of times specific word used in column range
- Count Occurrences of Specific Date in Range of Dates
- Formula to count number of time stamps within a range in a column having dates formatted as "custom"
- Any formula to count number of cells with a specific color pattern
- Using wild card characters in array formulas
- Counting the number of specific characters in a cell
- Formula to count number of times the letter "x" appears in a column
- Using wild card characters in array formulas
- Formula to count number of occurrences of a word, with some additional conditions
- Find and Count instances of a character in a string
- Formula To Count Number Of Instances
- How to find SECOND occurrence in a string?
- Want to count number of student grades in each group and subject
- Formula to count how much is saved in postcode area

I am after the formula to count the occurrence of, for instance the word 'the' in a sentence/paragraph that is contained in Cell A1. Cell B1 should return the quantity of times the word 'the' has been found in Cell A1.

Thanks in Advance,

Cheers,

Mark

Thanks in advance.

Trevor

For example, the cell might contain: smith,john(1234), smith,mary(1234),

doe,john(2345), etc etc.

I would like to be able to count how many times (1234) appears in the cell.

I tried using LEN, but that only counts one character.

Mary-Lou

Posted a problem last night regarding formula to count MIN of a column. Problem was solved. This problem is in the same vain as my last thread "Formula to count MIN with condition". In the attached worksheet samples, Col B has competition dates, Col C is time/distance data, Col C (filled down) is the formula that recogises if the time/distance is a PB. Once a season there is an event "REGION" where I want the formula to ignore the data in Col C adjacent to "REGION". The date of REGION changes every year and could be anywhere in Col B. The first attachment is what the sheet currently looks like and the second is what I want it to record, specifically D11. Even though D10 is a better distance, it would be ignored as it is REGION.

Thanks

RunHard

I'm looking for a formula to copy parts of an address in cells and paste in specific columns.

I attach a copy of a before and after (how i'd like it to look) sheet 1 - sheet 2.

Hope someone clever out there can help me with this.

Many Thanks

Marc

I would like to know if it is possible using a formula to evaluate the following.

I have a number of records that have in one cell a string of the form 1,2,3 etc (up to 10) representing conditions that have been met. There are a number of options available for producing reports on all of the other attributes in a record but now we wish to add this element as well.

The reports I can produce using VBA and the INSTR function, however on the summary page where, a total of all records matching available criteria is shown, depending upon drop down items being selected, I need to add this element to the equation as well. The existing conditions are counted using SUMPRODUCT and a combination of IF statements and work fine.

As there is an option to create a report for both AND and OR, e.g. if condition 1 and 2 and 3 apply include in the report, or if condition 1 or 2 or 3 apply include in the report

The conditions are selected using a check box and a drop down list to select the AND/OR condition.

I have been trying various combinations of database functions, countif, find and cannot get them to evaluate. I suspect that it may be beyond my reach to use a formula and I will need to use VBA with a Worksheet_Change macro to achieve what I want, or alternatively redesign the layout to store the conditions differently, however if anyone has any ideas for a formula I would much appreciate hearing from you.

A sample worksheet illustrates how the data is currently shown. The string in the record column is created using VBA and then inserted into the actual record.

Thanks for any ideas you may have

Regards

Jeff

Can anyone help with worksheet function for finding position of numeric

character in a string.

I am trying to extract data into the form 12a345 from data that may be like

any below:

12a345

12a345c

12a345_c

12ab345

12ab345c

12ab345_c

Extracting the first bit is easy with LEFT() but to extract the 345 I need

to find it's position in the string then I can use MID().

--

Regards,

Paul.

Can anyone help with worksheet function for finding position of numeric

character in a string.

I am trying to extract data into the form 12a345 from data that may be like

any below:

12a345

12a345c

12a345_c

12ab345

12ab345c

12ab345_c

Extracting the first bit is easy with LEFT() but to extract the 345 I need

to find it's position in the string then I can use MID().

--

Regards,

Paul.

I’ve found a couple formulas that work but they don’t update when a “@” is entered into a cell where it did not previously exist prior to entering the formula.

In Column E (E3:E400) I have a list of Start Dates

In Column F (F3:F400) I have a list of End Dates

In one Row (R3:BZ3) I have a list of specific dates (i.e. 08/31/08, 09/03/08)

I have attempted to create a formula in the following Row (R4:BZ4) that counts the number of times the date in the row above (R3:BZ3) occurs within the span of Start & End Dates in Columns E & F.

I have attempted several iterations of the SUMPRODUCT and COUNTIF solutions that are posted on this board, but I have yet to find a situation such as mine.

Any assistance would be greatly appreciated.

Jim

I looked for quite some time trying to figure this out on my own but

no luck. I created an inventory spreadsheet that takes a download from

Oracle11i of all transactions posted within a 24-hour period. The

downloaded information is on one worksheet and a second worksheet in

the same file uses formulas to count various types of transactions and

calculate cost from the download. In the download there are various

columns containing quantity transacted, from and to departments,

transaction types and a reference section for users to input comments

when they transact. Usually the file has about 3000 rows. All of these

files have a date time stamp of when the transactions were processed.

Example: "6/18/2007 23:50". I believe the default format is "special"

for the date but I use a macro to change the format from military time

to regular time. (The macro also cleans up the initial download, which

has a lot of duplicate information).

Problem:

Where I work there are two shifts, one starts at 5:45 am to 5:45 pm

(days) and the other is from 5:45pm to 5:45am (nights). What I don't

know how to do is count the number of transactions that occurred

within the shift time frames. I want to avoid using a pivot table if

possible to keep down file size. Is there a formula instead that will

count the specific number of transactions that occurred within my

given time frames? I tried using a count if formula, but noticed if

the data is a "custom" or "date" format the formula will not work.

Also if possible I want to avoid creating another column to compute

the formula with. I guess I'm just looking for a formula to count the

time stamps within the criteria using the original column.

Objective:

To count how many transactions were posted on day and night shifts.

Sorry for the long post, just wanted to give as much detail as

possible.

Deepest thanks all!

Sam

content of those cells) with a specific color pattern?

column. The column contains various 2-letter codes, and I want to count the

number of occurrences of codes starting with "L". I've tried using a wild

card character in the formula ("L?"), but it doesn't work. THis approach

works fine for regular formulas, but I think there's something to do with

Array formulas that prevents it from counting what I want.

Any suggestions out there?

--

PJB

e.g = Cell A1 = Dog Cat Animal

number of As = 2

number of Spaces =2

number of Ds = 1

etc.

any help will be much appreciated.

Thanks,

Rennie

A single "x" will be entered into a single cell

column. The column contains various 2-letter codes, and I want to count the

number of occurrences of codes starting with "L". I've tried using a wild

card character in the formula ("L?"), but it doesn't work. THis approach

works fine for regular formulas, but I think there's something to do with

Array formulas that prevents it from counting what I want.

Any suggestions out there?

--

PJB

I have a range of cells in one sheet containing data as follows:

ABC DEF_ABC ABC_XYZ . . and so onIn another sheet, I need to write one formula to count the number of times ABC occurs, another for the number of times DEF_ABC occurs and yet another for the number of times ABC_XYZ occurs. Note that DEF_ABC and ABX_XYZ need to considered distinct from ABC (i.e. not included in the count for ABC). This is only a sample list and the actual number of such strings I need to search for is much more. Is it possible to write a formula that satisfies these conditions?

Many thanks in advance!

Srikanth

VB:tells me the location of the first comma (2), but I want the total number of commas to be returned (7). I've been using a laborious character-by-character check from 1 to len(string) and incrementing the variable CommaCount every time it finds a comma, but I'm pretty sure there's an easier way.instr("A,B,C,D",",")If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

in a string. I can find the first occurrence using the FIND function no

problem but I have no idea how to find the position of the second occurrence

formulaically. Is there a formula to do that without running VBA?

In sheet "Dashboard" D5:L8 I want to count number of grades students get in each class group which is in column C. Class group will not remain constant and will change based on the data validation filter in C2. (I have kept Maths and English as an example)

Grades and Class group details from where results have to be calculated are in Sheet "Results"

Indirect reference ranges are in sheet "Indirect"

I tried using indirect countif formula but getting #Ref.

For example: Teaching group 11y/En1 has 17 students getting grade C (C3, C6 & C9)

Note: Grade A*= A*3, A*6 & A*9

Grade A = A3, A6 & A9, similar for other grades.

Could someone please let me know the formula that I can use to get the results.

Cheers,

Raj

new to the forum but i have read through a lot of posts and can find one relating to my query

i have a spreadsheet with 5 pages (5 different products) to it that tracks the orders from customers and the savings made on each of these

i have the postcodes of the customers and i would like a formula to add the savings made in geographical regions i.e. south west, midlands, london etc

now ive used formulas that i got of here to be able to count the frequency of the postcodes

which is useful but i really need a formula that will take all the customers with london postcodes and add all the savings (across the 5 products) made in that region to give one total figure each month

output of the formula can be just a cell with figure then put into graph for visual presentation

i attach a copy of my sheet with some basic made up data in it

and the formula used to count frequency of "ab** ***" postcode

can anyone help please