# Counting Certain Values

Jul 11, 2008
if i want a cell to count certain value in a range what formula o set, i know that CountA function counts cells with words in them but it counts everything, i want a certain word(s) to be counted.

for example i have...

New

Old

Take Over

and i want C11 to count 'New' in range of B12:B20 .

how to?

and 2nd how to Name the Columns, i mean Instead of regular A,B,C how to set a name or title for them...

and how to keep them on top, i mean as i scroll down columns keeps there only sheets scrolls up and down so i can see given Column Names/heads.

View 14 Replies
ADVERTISEMENT
Jan 1, 2014

I cannot get various formulas (Countif, Match, Frequency, Etc) to work properly.

I am trying to arrive at a total number of matches of numbers in cell range B1:G1 with any numbers entered into the cell range of K1:P11 and have the total of matches display in cell H1.

However I do not want to count duplicate numbers from the K1:P11 cells. (if the number 5 in posted in K1:P11 multiple times I only need it reported once in H1)

B1:G1 is the constant and the numbers will not change - K1:P11 cells will be populated by adding numbers until the all the numbers in B1:G1 is completed and match.

Range

B1 C1 D1 E1 F1 G1

2 7 19 45 22 13

H1 Total of matching numbers in cell range K1:P11

View 3 Replies
View Related
Dec 23, 2011

Is there a formula that would allow you to take the average of all values within a range but not count the zero values? I thought something like this might work but it's not. Neither one worked.

=AVERAGEIF($E$4:$E$34,">0")

=AVERAGEIF(E4:E34,">0")

View 9 Replies
View Related
Jan 25, 2004

I have a collumn with lots of different text values some repeated. how can i count all these values so that it only counts each value once.

e.g

if in cell A1 i have = "apples"

and in cell A2 i have "apples"

and in cell A3 i have "Pear"

View 9 Replies
View Related
Mar 5, 2014

I'm trying to display a 2-column table that tells me the number of games broadcasted by each channel. The code has to figure out the channel name itself, without me specifying the channel name in it. How can I do this in VBA?

Game1 NBC

Game2 CBS

Game3 CBS

Game4 CBS

Game5 FOX

Game6 CBS

Game7 FOX

Game8 FOX

Game9 FOX

Game10 NBC

Game11 ESPN

Game12 NFL Network

Game13 FOX

Game14 CBS

View 3 Replies
View Related
Jun 20, 2014

I have a cell, say C74. In the cell contains 416 values are four & five digit numbers separated by a comma and a space. (example: 21160, 21161, 21250, 21340, 21440, 2155, 21650, 21651, 21740, 21741, 2185, 21940, 2253, 2254, 2440, 26160, 26250, 26293, 26323, 26350, 2636)

So I'm trying to write a formula to count all values with in the cell.

Answer should be "21"

View 5 Replies
View Related
Jun 27, 2014

I am trying to count how many times a certain value appears in a column. For example how mnay times A81001 appears in column A and compare with column B. I suppose it's Count function?

column A column B

A81001A81001

A81001A81001

A81002A81002

A81002A81002

A81002A81002

A81002A81002

A81002A81002

A81003A81003

A81003A81003

A81003A81004

A81004A81004

A81004A81004

A81004A81004

A81004A81005

View 12 Replies
View Related
Dec 10, 2013

I'm putting a presentation together for work regarding false alarms and I'd like to count how many alarms have gone off at certain locations and I'm wondering if there is a formula that will accomplish this. I've attached a pic of a spreadsheet containing the data to be used. On the left side, there is a table labeled "Alarms" with columns displaying the dates, street numbers and street names. On the right side of the spreadsheet, there is a table labeled "Activations", which will be used to display the amount of single, double, triple, etc. activations.

For example, using the data, the alarm at "2 Red Oak Row" has gone off 3 times and since there are no other addresses where this has happened, a "1" would be displayed under the box labeled "3" in "Activations". Since the alarm at "4 Winding Way" and the one at "23 Blackberry Lane" have both gone off twice, a "2" would be displayed under the box labeled "2" in "Activations". The same would hold for addresses where the alarm has gone off 3 times, 4 times, 5 times, so on and so forth. In addition, if an alarm has gone off 10 or more times at any residence, the first 10 activations and any subsequent activations would be counted as a "1".

For example, if there is one case where an alarm at a certain residence has gone off 10 times (or more), a "1" would be displayed under box 10. If another residence's alarm also went off 10 times (or more), a "2" would be displayed under box "10".

View 5 Replies
View Related
Nov 11, 2009

Ok so I have an excel file, that has 20,000 records/rows of data. There is a column called <STATE>, which could be PA, MD, CA, MT, etc.

I need to know how I can count how many times each state is populated and return the results to a new created sheet called <REPORT>. See example below.

State:

PA

PA

PA

MD

CA

MT

NJ

NJ

Report Results

PA 3

MD 1

CA 1

MT 1

NJ 2

Can this be done?

View 9 Replies
View Related
Nov 19, 2009

I'm trying to find a code that will count duplicate values in columns B,C and D and put the outcomes in B1, C1 and D1.

If've tried conditional formatting but can't count any formatted cells with duplicate values.

Any Ideas?

View 6 Replies
View Related
Jan 25, 2004

I have a collumn with lots of different text values some repeated. how can i count all these values so that it only counts each value once.

e.g

if in cell A1 i have = "apples"

and in cell A2 i have "apples"

and in cell A3 i have "Pear"

i want this to return the value 2 not 3, make sense?

how can this be done via a formulae or macro?

View 9 Replies
View Related
Jan 30, 2007

I have a sheet with a bunch of data including dates. I'd like to report out the number of cells that are in a certain month (say January). If I create another column and use =MONTH(A12), I can use COUNTIF(A1:A100,1) to perform this task. This somewhat clunky solution isn't very practicle, becuase I have about 30 column's I'd like to analyze by the date in the column. Is there a more elegant solution where I can incorporate the MONTH(A12) into the COUNTIF equation?

View 9 Replies
View Related
Feb 4, 2007

I have a 2 columns with lots of different text values some repeated. Again Colum B has a Yes/No anwer input, how can i count all these values in Colum A based on whether the Column B value equals yes.

Column A / Company Name_____________Column B / Reply

Company X ___________________________Yes

Company X ___________________________No

Company Y ___________________________Yes

Company Z ___________________________Yes

Company Z ___________________________Yes

What I want the formula to do is count all the Yes occurrences in column B and Only one distinct occurence of a Company

eg

From the above I want to see 3 as a result as opposed to 4 as they are two Yes entries for Company Z

I have been able to use the SUMPRODUCT function but dont have the knowledge to build upon this to take the second column value into consideration.

View 9 Replies
View Related
May 7, 2014

I have 2 columns with approximately 200K lines of data. There are only 84 and 823 unique values in each column respectively. On my report.

Column A is essentially a customer list, column B is a product list. I am trying to find out how many unique/individual products a customer orders. I have a separate column that has the customers in it (Column G), and in Column H, I want it to count the unique products ordered by customer.

I changed some of the data, but essentially what I attached is what I need.

View 8 Replies
View Related
Dec 11, 2013

I was trying to see the values only if the same value arrived more than three times in the given range.

Ex:

ABCDEFGHIJ

1AAAABAA

2

in the given range A1:J1, I would like to count 'A' only if it repeats more than or equal to 3 times. For row 1 I should get count as 4.

View 1 Replies
View Related
Jul 18, 2014

I have been trying to adapt a couple of the posts I've seen on this topic but they seem to refer to counting in rows and I'm having trouble translating that to columns.

I have a simple situation. I have 1s or 0s in columns from left to right. I only want to count the first occurrence of consecutive "1"s and only if the series starts with "1" in the left most column. (I am trying to calculate consecutive years of giving and the columns are FY14, FY13, FY12, etc. Only people who gave in FY14 would count.)

Example:

Row 2: 11101010

Row 3: 11001011

Row 4: 00100110

I would like Row 2 to count "3", Row 3 to count "2" and Row 4 to count "0".

View 2 Replies
View Related
Jun 8, 2009

I have a column, we'll say E18:E2500. In the cells in that column are four digit numbers. Some of these cells may have multiple four digit numbers separated by a comma and a space. (example: 2020, 2100, 3120) Some other cells in the column may also share the four digit numbers (I mean duplicates).

So I'm trying to write a formula to sum and count all the unique values in the cells and in the range. This is what i've been trying to use but it counts all the values with no regard to duplicate values:

=SUM(IF(LEN(TRIM($E$18:$E$2500))=0,0,LEN(TRIM($E$18:$E$2500))-LEN(SUBSTITUTE($E$18:$E$2500," ",""))+1))

View 5 Replies
View Related
Aug 6, 2009

I noticed that when I use countif to count cells with certain text value it works but up to some point when it returns way too much then (when there are generally more values matching I think). I don't know what is the cause ..formatting? some function limit ?

View 3 Replies
View Related
Feb 16, 2006

I have a column of data with 1000 rows. In this column there are duplicated values. I would like to be able to use a function count how many unique values are in the column. Let's say there were 4 duplicates of 250 values in the column. I would like to be able to write a function to calculate the 250. I am very familiar with Excel and am able to acheive the number through subtotals and/or pivot tables. I know I can find how many instances one specific value appears in the column through sumproduct and/or countif statements,

View 14 Replies
View Related
Dec 2, 2008

I'm working on an attendance sheet, and have allocated certain letters for related occurrences. For example, V=Vacation day, S=Sick day, B=Bereavement, etc. Over a two-week period (eg - D19:D32), I want to total the number of times one of these values has been used, and add to work hours. Where an employee will enter 7.5 in D19 to indicate hours worked, they may instead enter a 'V' for a vacation day, and have 7.5 hours still added to their total hours in the pay period. I have a formula that works, but it is so incredibly long that I'm thinking there must be an easier way.

Right now, I'm using the following:

=SUM(D19:D32)+(COUNTIF(D19:D32,"V")*7.5)+(COUNTIF(D19:D32,"Vh")*7.5)+(COUNTIF(D19:D32,"S")*7.5)+(COU NTIF(D19:D32,"Sh")*7.5)+(COUNTIF(D19:D32,"H")*7.5)+(COUNTIF(D19:D32,"B")*7.5)+(COUNTIF(D19:D32,"A")* 7.5)

View 3 Replies
View Related
May 2, 2009

I have a spreadsheet with data from a survey. There are 375 respondents (rows) and 26 questions (columns). I use COUNTIF in order to figure out the frequency of responses and SUMPRODUCT to do some finer analysis. Here's my problem, some of the questions have multiple valid responses. In those cases the data was entered into the same cell. So I have a column that looks like this:

1,4

2

1

3,12

1,11

8,

3,14,9

I can't figure out how to count the number of "1"s. It would be much easier if the responses could only be 1-9, but unfortunately they can be 1-20. So far, I've come up with:

View 3 Replies
View Related
Sep 12, 2009

I need to do is, on sheet 2 is to have a formula which will count all cells in column A containing dates from 1/1/2009 to 31/1/2009, Another column will count all dates from 1/2/2009 to 28/2/2009 and so on. I have tried the countif format but this does not seem to work.

View 4 Replies
View Related
Jan 1, 1970

I have created a calendar in Exel for individuals to put in their time at the bottom for everyday I have put in a total. Is there a formula that I can sum the dates, pick a certain time from those date and get my total hours for that particular week?

View 6 Replies
View Related
Jan 31, 2013

what function can I use to count the number of unique values in a range of cells?

View 4 Replies
View Related
Apr 18, 2013

I am trying to count multiple occurences.

Instrument Name

Times Traded

AEON CO LTD

6

ASICS CORP

3

ELECTRIC POWER DEVELOPMENT C

10

In column C (Times Traded) I have used a simple countif formula: =COUNTIF($B$2:$B$8068, C2)

Now I have to check for how many times this instrument was bought or sold. This is in column A .... How will I count the Buy values ... cause this formula I devised doesnt work =COUNTIF($B$2:$B$8068, and(C2, A2:A8068="Buy")

View 4 Replies
View Related
Jul 3, 2013

I'm looking to find a solution to count the totals for values which are linked to a specific code. For example:

A b

ex1 22

ex4 18

ex3 10

ex4 3

ex1 23

ex2 11

So I want a formula that will count the totals for each group in column A

The answer would be

ex1 45

ex2 11

ex3 10

ex4 21

View 2 Replies
View Related
Jul 25, 2013

I have a column of data (say in cells J1 to J20) such as

AB*

CD+

EF&

etc etc - not every cell has an entry.

In another column elsewhere, each of these items has a number associated with it, e.g.

AB 3

CD 3

EF 4

Note that the symbol (*,+,&) is not in the table.

What I want to do is this: For each value in column J, I want to lookup it's corresponding value in the table and add 1 to a count if the value is say 4 (so in the example the cell containing my result would be 1 because only EF has a value of 4). If I wanted a value of 3 my count would be 2 (because AB and CD both have a value of 2).

I think I need a combination of countifs, vloopkup and left (as I need to strip off the number in row J somehow) but I can't get the syntax right (left for example won't work on a range).

View 1 Replies
View Related
Sep 18, 2013

I have the following macro (as part of a much bigger set of code that adds some formulas to to cells to return the total number of unique values. The problem is, this part of the macro takes over 10 minutes to run. Is there a quicker way of doing this or am I going to have to just wait it out each time.

The columns B and/or H is a formula. Column W is an string value.

FYI the last row it is counting to is between 4000 and 30000 lines in each of the formulas

Code:

Dim IRMatRow As Integer

Dim IRMisRow As Integer

Dim UKMatRow As Integer

Dim UKMisRow As Integer

IRMatRow = Worksheets("Matching Stats").Cells(Rows.Count, "H").End(xlUp).Row

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

View 2 Replies
View Related
Jan 17, 2008

How can I count the number of unique values in a column?

I have a spreadsheet that lists transactions that occurred over a one-year period (2007). I need to determine the number of days our facility was operational. No transactions on a particular date (25-Dec-07, for example) and the facility would be considered to have been closed on that date.

On the days that the facility was open, multiple transactions would have occurred on those open days and those multiple tranactions are all reflected in the worksheet.

So I need to count each occurrence of '01-Jan-07', '02-Jan-07', and so on.

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