Count Two Different Text Values Within Two Columns

Jun 1, 2006

I need to count two different text values within two columns.

Column A contains sentences and Column B contains various values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:

What I need to do is count groups of records, like only counting the records
that contain “XX” anyplace witihin Column A plus have “XXXX” in Column B.

View 14 Replies


Count Unique Values In Column Based On Values In Other Columns

Mar 14, 2014

I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.

My current formula in Summary tab D4:D19 is

This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).

The results in the pink highlighted cells (Summary column D) should be:

Names starting with A - 3
All others - 2

I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.

View 2 Replies View Related

Count Unique Text Values (number With Text And Quotation Mark) With Formula

May 14, 2012

I am trying to find a formula that will count the number of unique entries there. I have tried the solutions posted on various websites to no avail (most recently:


The answer should be 4,457.

Ticket Number


View 1 Replies View Related

Count Combinations Of Values In 2 Columns

May 9, 2008

Have a monthly report process in which I need to count to combinations of values in 2 columns. Example:

A 1
A 1
A 2
A 3
B 2
B 1
B 3
B 3
B 4

Count results: A&1 = 2, A&2 = 1, etc. I'd like to get a summary table of the results. I see how to use sumproduct to count combinations, but that requires a sumproduct formula for every combination. The 2 colums are lengthy and the contents vary each month, so building the formulas each month will be a fair amount of work. Is there another way?

View 3 Replies View Related

How To Count Rows With Defined Values In Multiple Columns

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

Compare Data In 2 Columns With Values That Have Exceeded 1,048,576 Count

Jul 22, 2012

I have an Excel macro which works well in comparing two columns A and B. It populates column C with unique values in A and not in B, populates column D with unique values in column B and not in A and puts the values found in both A and B in the fifth column (E).

Data in the columns A and B of Sheet 1 has exceeded 1,048,576 (the maximum allowable in Excel 2007) and i will copy the overflow data in Sheet 2's column A and B and i will like the comparison to continue in the next worksheet.

I need the macro to treat the second worksheet as a continuation of the first sheet and not as a separate data. (The values i am comparing have exceeded 1million).

Sub twocols()
Dim d As Object, na&, nb&, a, b
Dim e, p&, q&, r&, m
Set d = CreateObject("scripting.dictionary")

[Code] .......

View 2 Replies View Related

Automating Count Of Various Text Values

Mar 11, 2014

I have 300 cells filled with around a dozen different names - i.e. the same names crop up multiple times. What I need is automatically creating a list of all the names that appear, ordered by how many times they appear and with the number of times they appear next to them - i.e. ranking the names by their frequency.

View 3 Replies View Related

Excel 2003 :: How To Show Differences And Count Between 2 Columns Of Text

Aug 5, 2012

Have Excel 2003. I have

List of email addresses in column a
List of email addresses in column b

** I would like a formula that will take all the values in column A and compare it to the entire list of entries in column B.
Would like it to show in Column C any entries in Column A that ARE NOT in the entire column B.

*Would like it to repeat for entries that are in Column B, but not in Column A and show in D.

*Then would like a count of the differences for each column (that is pretty easy).

It needs to look at the entire list of entries in the column as these will be email addresses. We want to know what is missing from Column A that is not in Column B and what is missing from B that is not in A.

View 3 Replies View Related

Count Text Values Based On Second Cell

May 16, 2012

I have a list that changes:

Apples 1
Apples 1
Bananas 1
Carrots 0
Carrots 0
Donut 1
Donut 1
Donut 1
Donut 1
Egg 1
Fish 0

I would like a to create a function in one cell that counts the unique text values in column A, but only when there is a corresponding 1 in column B. So the answer in the above example would be 4 (Apples, Bananas, Donut, Egg).

View 2 Replies View Related

Count The # Of Rows In A Spreadsheet In Which There Is Non-blank Text Data In 2 Separate Columns

Dec 5, 2008

I am trying to count the # of rows in a spreadsheet in which there is non-blank text data in 2 separate columns. For instance, if the spreadsheet looked like the one below (dashes just for formatting purposes):

Mary-------some text--------some more text
John-------<blanks>---------just text here
Sue--------just some here-----<blanks>
Dave-------something--------something else

The total # of rows with something in both the "THIS" and "THAT" columns above would therefore be 2.

View 4 Replies View Related

How To Count Values Of Numbers In A Row Next To Text (True Or False)

Feb 27, 2014

I need to count values of numbers in a row next to text(True ot False) Like:

True | 150
True | 66
False| 40
True | 78
False | 56


True: 294
False: 96

View 3 Replies View Related

Count Frequency Of Continuous Occurrence Of Text Value In Range Of Values

Dec 9, 2013

In an employee attendance file I am trying to count the number of times an employee has taken 3 or more days of leave together (continuously) in a month. My attendance file looks something like this


[Code] ..........

In the example above E001 has taken 3 continuous days leave twice so formula should return 2, for E002 & E003 the answer would be 1 each.

View 4 Replies View Related

Count Values In One List And Then Narrow / Combine Values Based On Criteria And Count Those

Jan 20, 2014

I need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.

Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.


View 2 Replies View Related

Counting Text Values From Two Columns

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

Count Unique Values / Text Within Range Of Cells That Contain Duplicates / Blanks And Errors

Jun 25, 2014

How I can create a simple formula to count unique values/text within a range of cells that contain duplicates, blanks and errors?

For e.g., in Column A (row 1 - 10):



View 3 Replies View Related

Excel 2010 :: Count Distinct Text Values In One Column If Another Column Equals A Value

Mar 5, 2014

I am trying to count the distinct times a value shows up in a column, if another column has a 2 in it.

For example:
A, B

The results shoud be: (2) Because I only want to count the P25 one time.

I'm using Excel 2010

View 2 Replies View Related

Count All Unique Values (text) + Definition Of UNIQUE :D

Aug 24, 2009

If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?

View 14 Replies View Related

Count Formula: Count Total Entries In Columns

Feb 22, 2007

I have been using the wrong formula to count total entries in columns and only just found this error. The MAX formula in cell B4 is: =MAX($B$12:$B$36). If the all the rows are full within range F12:F36, then the MAX formula is fine to count the total within range B12:B36 (25) so I thought. But sometimes there are omissions between F12:F36. If there are 2 blank cells anywhere within F12:F36 for example, then B4 needs to show 23 respectively. In the sample WkBk B4 needs to show 8

View 2 Replies View Related

Count Intervals Of 2 Numeric Values In Same Row And Return Count Across Row

Sep 23, 2005

I require a Formula to calculate the INTERVALS (the number of Rows between
the LAST instance and the PREVIOUS instance in a column) between each
individual occurrence of any designated PAIR of Numeric values (single-digit
/ double-digit) in the same Row of the Named Range "Results" and return each
calculated INTERVAL result to a separate Column on the same Row of a New
Sheet - starting with the most recent ( the LAST) occurrence.

For instance, each time 80 and 87 appear together in the same Row, return the
INTERVAL by calculating the number of Rows between the LAST instance and the
PREVIOUS instance in a column - locate when both Numeric values LAST appeared
together and Count back to their PREVIOUS appearance together to get the
required Count; i.e. count from the Row ABOVE LAST appearance to the Row

The results are returned to a chart / matrix layout: I have the criterion
vertically and horizontally and they are referenced using the horizontal and
vertical cell address that houses each criterion, and the results are
returned across the Row of the intercept of the vertical and horizontal
criterion. At some point both criterion values being referenced will be the
same, can the Formula return empty text "" when this occurs?

Example Chart / Matrix Layout:
Cell Ref. A2 and B1 criterion 80 and 80
Cell Ref. A3 and B1 criterion 81 and 80
Cell Ref. A4 and B1 criterion 82 and 80

Criteria B1 houses 80
A2 houses 80
A3 houses 81
A4 houses 82
A5 houses 83

View 12 Replies View Related

Macro To Give Count Of Unique Values After Comparing Comma Separated Values?

Mar 2, 2014

I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".

The sample sheet has been attached for reference.

View 3 Replies View Related

Text File Import With Text To Columns Splitting Same Text Differently

Nov 21, 2007

I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.

I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.

FieldInfo:= _
Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
Array(102, 1))

View 6 Replies View Related

Count Unique Values, For Specific Values

Sep 13, 2006

I have an array that I'm using - and it works perfectly: =SUM(If((FREQUENCY(If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""),If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""))>0),1))

Now, what I would like to do, is see how many of these unique numbers relate to another number in column D
See attachment.

View 4 Replies View Related

Counting Formula (compare Values In 2 Separate Columns To See How Many Times The Same Value Appears In Both Columns)

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

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

Excel 2013 :: Set One Columns Text To Color Based On Another Columns Results?

Apr 9, 2014

how to set one entire columns text to two different colors based on another columns values. So for example I have column A and B. Column A has two values called Internal and External. Column B is a title table so the entire column is just titles. We'll say it goes for 20 rows if you need a row count. What I am looking to do change the text in Column B to Red for External and Blue for Internal. I tried the conditional formatting and I just can't seem to find the right option.

I'm using Win 8.1, Office 2013.

View 4 Replies View Related

Fill Values And Replace Told Values By Vba Code Depends Upon 2 Columns

Mar 26, 2014

I have set of data in multiple range ,need to fill the and replace the old values depends upon two column values (AH & AL)

IF Active Calls is "TATA" In AH:AH, and IF Action Onwer Col is "Blank",in AL:AL
Then Fill the Blank cells by Values "SVC" in the col Action Owner,Then Replace Old values by "Updates Awaited" in Status Col(AM:AM)

Find the attachment & basic code take this code for this task

[Code] ....


View 5 Replies View Related

Align Matching Values In Columns A And B And Bring Corresponding Values With Column B

May 15, 2014

I would like to align the matching values in columns A and B but I want the values that correspond to b in c,d and e to go with the column b value.

C D and E don't necessarily have a value in there but if it does it needs to move with B

View 5 Replies View Related

Text To Columns/time Formatting Split Them Into Columns

Jan 5, 2010

I've got some time values in an Excel Sheet in the format hh:mm:ss. I need to split them into columns (including the colon) like below:

hh: | mm: | ss

I can do this manually using text to columns but when I use text to columns in my macro, it automatically changes the time format to h:mm:ss PM

View 2 Replies View Related

Look Across Values In 12 Columns, Return The Number Of Unique Values

Apr 17, 2009

I have 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows.

What would be the best way to do so?

Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns.

I have attached an example file that represents how the data is stored and the output I would like (Unique Count).

View 7 Replies View Related

Insert Row (Based On Values In Range) With Sum Of Values For Other Columns

Apr 21, 2014

I have different reports, some have fifty transactions, others have thousands. My goal is to: Insert a new row every time the values in the "Account" column meet a certain criteria, AND THEN add the totals for the Debit and Credit Columns.

Let's say I start with a table that looks like this:



[Code] ....

I want to group the first four rows because Accounts 33010 and 33015 are in the same department. Same with 50050 and 500060. I want to then insert a row below the last row with "33015" as its Account #. And add the values for Debit and Credit. It'd look like this:


[Code] .........

Honestly, I have tried everything. Running a Macros with Relative reference does not cut it.

View 8 Replies View Related

Compare Text Strings In Two Columns And Return Text From Adjacent Cell

Feb 28, 2013

how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?

View 6 Replies View Related

Copyrights 2005-15, All rights reserved