# Count Text Values Based On Second Cell

May 16, 2012
I have a list that changes:

A B

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

Book1.xlsx

View 2 Replies
View Related
Mar 9, 2014

I'm tying myself in knots when trying to combine logical formulas to return a text value based on multiple cell values.

Cell A1 can equal 1-10, and cell B1 can also contain the values 1-10.

I want C1 to display the text "YC" if B1 value is equal to 9 or 10. I want C1 to display the text "SC" if B1 value is 5-8, and A1 value is 4-10. Any other combination of values should return a blank cell.

I tried splitting this into two components before trying to make it fit into a single equation, and i can achieve the first condition of display "YC" =IF(AND(A1>=9,A1

View 3 Replies
View Related
Apr 11, 2014

I'm creating a workbook to keep track of my utilities payments, one sheet for one utility and so on. I like to copy two cells from each sheet to another one to keep me updated of the amount to pay and the date. an example: column A with text, if text "NEXT" appear in column A, copy the value of two cells (at columns B & C) at the right of "NEXT" to another worksheet, if that possible? Below is a photo as an example:

excel.jpg

View 5 Replies
View Related
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:

Code:

=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))>0,1))

).

The answer should be 4,457.

Ticket Number

T20110819.0527

T20110830.0339

T20110901.0060

T20110901.0060

T20110907.0042

T20110907.0042

T20110908.0186

T20110908.0186

T20110908.0186

T20110908.0186

[code].....

View 1 Replies
View Related
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

{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,

IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))

+

SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,

IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}

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
Feb 26, 2014

I'm trying to use Excel to build a text file based on values from certain cells in Excel 2010. This is based partially off of static text that never changes and variables that will need to change. For example, I want Excel to output a text file that has the following text: The red fox jumped over the $X twice. I would want $X to be replaced by the value of A1 of the active worksheet. I will also have several lines like that, so it won't just be a single line, but anywhere from 20 - 120.

View 1 Replies
View Related
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
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:

AAAA

BBBB

BBBB

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
View Related
Jul 1, 2009

I am using Excel 2007, and I'm stuck with this problem.

View 4 Replies
View Related
Feb 19, 2009

I'm facing a big problem and I can't found a solution for days.

I'm trying to count/sum unique values in a Range, base on a criteria excluding Blank cells. Basicly I'm using the Formula Below:

=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))

Let me Put to you an example for you been able to understand my problem.

Example considering Range "C2:C20" exluding blank cells

(1) - To count unique values

=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))

(2) - To count unique values based on criteria in range "B2:B20"

=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E"))

(3) - To sum unique values on "E2:E20" based on same criteria

=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")*(E2:E20))

They all work fine. But I need to insert a merge at Row 10 for

better reading purposes and the (2) and (3) are returning (#DIV/0). The (1) formula still work's.

I just don't know what to do guys ....

View 9 Replies
View Related
Feb 27, 2014

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

A1____A2

True | 150

True | 66

False| 40

True | 78

False | 56

Output:

True: 294

False: 96

View 3 Replies
View Related
Aug 8, 2007

I have a list of YES or NO answers in a sheet. I want to calculate the % of YES in the list. E.G. if there are 100 cells in total and 25 of them say YES and the remaining 75 say NO, the percentage should be 25%.

View 2 Replies
View Related
Jan 9, 2007

I have a worksheet with two tabs.

First Tab

Account Name

Account Number

Second Tab

Account Name

Account Number

Account Ship Location Number

On the first sheet each account name and number only appears once.

On the second sheet there may be multiple entries for each Account Name and Number.

On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.

View 9 Replies
View Related
Aug 27, 2010

I was wondering if there was an array formula or some VBA code which can transpose/repeat values based on a count. Below is an example of what I mean:

Name Count Output Tod 4 Tod Bill 3 Tod Sue 5 Tod

Tod

Bill

Bill

Bill

Sue

Sue

Sue

Sue

Sue

View 6 Replies
View Related
Mar 18, 2013

I've got two columns, one with policy numbers, the other with error codes.

A policy could be in the list more than once, so I'm counting the number of unique policy numbers with the function

=SUM(IF(FREQUENCY(A10:F10000,A10:A10000)>0,1))

I want to count the number of errors that are NOT code 00 or code 21. Because the error codes are saved as text, I'm counting the number of errors with

=SUMPRODUCT((B10:B10000"21")*(B10:B10000"00")*(B10:B10000""))

The problem is that since a policy number could be listed twice, when I count the errors, I might be counting the same policy as an error twice. This means that I could feasibly end up with more errors than I have unique policies, which doesn't work.

What I'd like to do is only count the number of errors that have unique policy numbers. That is, I only want to count an error the first time it is in the list, based off it's policy number. Is that possible?

Here's my sample data: [URL]

View 9 Replies
View Related
Jul 27, 2009

I need to count the unique values in a list based on another value. i.e.

AB ___ Jones

AB ___ Smith

BC ___ Henry

AB ___ Jones

BC ___ Brown

BC ___ Henry

AB ___ Tomkins

For all the AB's in column 1, I need a formula that will give me all the unique names from column 2, in this case 3. I've tried adapting:

=IF(LEN(June!D5:D100),SUMPRODUCT(1/COUNTIF(June!D5:D100,June!D5:D100)))

but haven't been able to get it to work.

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

sat

sun

mon

tue

wed

thus

fri

[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
Feb 22, 2011

I'm having a problem with a spreadsheet in Excel. I have in column terras, codti problem and several cells that are repeated. I'm stating that terra appear only once, the information in column each issue should appear in different columns with the sum of how often they appear and codti according to terra.

In excel is best illustrated what I mean!

In total, 5267 lines and need to do this with all. You can do this in excel?

View 4 Replies
View Related
Jan 24, 2014

I have column in my table that calculates time in hours between now and ticket open date (ticket open date in B1)

so, in C1 i have following formula:

=(now()-B1)*24

Results are in hours, such as 15, 25, 32, 65 etc

In pivot table, I would like to see how many tickets with values of less than 24 or more than 48, etc

My available columns are: Region, Ticket#, AgingOpenHr(this new column).

I tried it different ways, and I cannot get it Less Than and Greater Than work under Values, works if I select grater than or less than in Label, but label does not produce consistent numbers.

View 1 Replies
View Related
Nov 25, 2009

I am in desperate need of a function that will count a column of data where there are blanks and values based on a certain date that will also capture any data that is added after refreshing the table from Access. I have tried several functions but this is what I have: =(ROWS('TouchBack Detail'!$Q:$Q)*COLUMNS('TouchBack Detail'!$Q:$Q))+(COUNTIFS('TouchBack Detail'!$B:$B,'Nov TouchBack Summary'!B$1)). The result should be 3 but its including all other cells in the column that are not and should not be included in the refreshed tables data (Table_TouchBack.accdb). I have attached the spreadsheet for review. The function is in cell B27 highlighted in yellow.

View 3 Replies
View Related
Jun 27, 2012

I can't seem to get a formula to work. I am trying to count the number of times "EE" shows up in a range only if "CR" is listed in that row within another range. For simplicity Column A has a list with the following values scattered from A2 through A100: CR, LT, ST, DI. Column B has a list with the following values scattered from B2 through B100: EE, EF, EG.

I need to know how many EE's are listed in column B if CR is on the same line in column A.

View 6 Replies
View Related
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):

Proj-001

Proj-001

Proj-002

Proj-004

#N/A

#N/A

Proj-007

Proj-002

View 3 Replies
View Related
Mar 5, 2014

I am able to quite easily count the number of specific values in a cell after the table has been filtered. However, the problem I have run into is that some times the data needs to be placed into the spreadsheet twice (or to be more specific the same subject is associated with several unique data points).

What I need: some way to count the instance of some give value in column D only once based on the presence of a duplicate (unique) identifier in column C. However, when I filter the entire database, it must count *only* the filtered cells and not the hidden cells as well.

Picture:

Column C Column D

111111 M

111111 M

111111 M

[Code]....

Currently calculates: M=9, F=2

Right now it incorrectly states there are 9 "M" from column D when it really should be 5 since 3 are duplicate values. My main difficulty is making sure this continues to work after I filter the entire sheet (say column ZZ) and have a bunch of hidden cells.

Equation currently using to count only filtered values (in this case "males" and "females"):

="M = " & SUMPRODUCT(SUBTOTAL(3,OFFSET(D3:D13,ROW(D3:D13)-MIN(ROW(D3:D13)),,1))*(D3:D13="M"))

View 9 Replies
View Related
Aug 8, 2012

I am not sure if this is possible, but worth a shot. I am using a countif function to count a cells and I want it to count only if it has text, followed by a space then more text. I've been able to count the text only followed by a space, but cant figure in the more text part.

View 2 Replies
View Related
Nov 8, 2006

I need a formula that will calculate the sum total of 6 and a longer formula to cope with 12 letters in a range. The third value in both ranges is worth double points.

The letters and their corresponding values are:

F=0, P=2, M=4, D=6

Some 6 letter examples to illustrate:

in A3:D3 the letters D,D,D,D,D,D return the value of 42 in E3 (6+6+12 (third letter is worth double)+6+6+6

in A4:D4 the letters P, P, F, F, M, M return the value of 12 in E4

Some 12 letter examples to illustrate:

in A3:L3 the letters D,D,D,D,D,D,D,D,D,D,D,D return the value of 78 (6+6+12+6+6+6+6+6+6+6+6+6)

If the range is empty the formula should leave the cell blank.

View 9 Replies
View Related
Oct 26, 2009

I want to sum all expenses on Company B within Expense 1. Is it possible to use MATCH/INDEX or something else to do that? I can't use SUMIF, because I don't want to include Company B within Expense 2 for instance.

View 5 Replies
View Related
Nov 17, 2013

I want to put in place a CF icon set of either a green tick or a red cross in cells A1:A20 but when I get to "Manage Rules option box" It is only showing me that I can use "values". Is there a formula that i can use to make it choose the icon set based on text of either "y" or "n" in cells A1:A20 .

I have tried to just change the font to "wingdings2" but other need to use the worksheet and they wont know to use the corresponding text of "O" & "P" .

View 4 Replies
View Related
Feb 27, 2008

I have columns of data that Im trying to count the number of times that one columns numbers fall between 2 other columns range. For the numbers in column F Im trying to get a count of the number of times that the numbers appear between the ranges in columns C to D. The total would increment in column E as these numbers were found in the range. Example: The numbers 60 (F2) and 150 (F3) would appear in the range 1 (C1) to 156 (D1). The count would be 2 which would appear in cell E1.

View 8 Replies
View Related
Dec 17, 2013

Count if cell contains text string

I have a list of animals and a list of countries they are found in so I wanted to count how many animals are found in each country.

So the count function =COUNTIF(E3:E202,"*" & "India" & "*") works perfectly to count how many have India listed as one of the countries.

But was wondering if there is a way to replace the criteria bit (ie the country name) with information from a cell.

So basically I have copied in a list of countries so I just want to use that as the criteria without having to manually input them all individually.

View 1 Replies
View Related