I'm just using the "=COUNTIF" function to count how many times a particular website was repeated, but I have no idea if such website is among the top five that appear the most throughout. Finding that manually, given the ridiculous size of the data provided, would take days!
View 4 RepliesHow I can count the number of times each unique phrase in row "A" is repeated?
For example if my data set was
Blue
Green
Black
Green
Red
Red
Red
Red Hat
how can I get excel to count the number of times and return data like
Blue 1
Green 2
Black 1
Green 2
Red 3
Red 3
Red 3
Red Hat 1
I have a excel doc with 8000 names I need to delete the people who are listed on 7 rows.
There are no blank rows and Colume A has the names sorted.
I have 1 to 30 spread sheet plus a Sheet with TOTAL , each represents one day ,
on each sheet I have one column and 6 lines where I will please random numbers from 1 - 50 but six number on each sheet
The question is on the total sheet I would like to have automatically the number repeats the most on those 31 sheets .
i have one Q??
i have these times
07:00
07:43
04:55
07:43
is it possible to COUNTIF the repeated hours? the result should be 3 "07:??"
I have a table with some reapeated words in, I wondered if anyone had any methods of counting words and displaying the total in number format. for example if I had in cell A1-Hello and A2-Hello and A3-Bye how can I get Hello = 2 and Bye =1
View 9 Replies View RelatedI have a spreadsheet (>10,000 rows) with data referring to a grid cell map. Each grid cell on the map has an ID and associated data. About 10% of the grid cells have duplicate records, but with different data. I need to produce a spreadsheet with a single record for each grid cell that contains the data from each of the duplicate grid cells. Some occurrences may be >2. Example below.
Current
[TABLE]Grid#;Data
1;a
1;c
2;b
3;a
4;b
4;c
5;d
5;d
5;f
[/TABLE]
Needed
[TABLE]
Grid#;Data1;Data2;Data3
1;a;c
2;b
3;a
4;b;c
5;d;d;f
[/TABLE]
I want to count the nr of words in cell without counting the repeated words.
For example, in "this is a ball and that is a square", the total nr of words is 9, and the total nr of non-repeated words is 7, because "is" and "a" are repeated twice, and I just want to count it once.
I have a table of data, from which I have created lists for “items created within a period” (the items are paintings on canvasses).
Each list displays which canvasses were completed within 2 dates.
My main data table is large (over 1000 lines and growing) and I need to extract the total quantity of each canvas size that appears in each period list.
My sample attached shows sample data, with corresponding lists of canvasses created within each time period, together with the result I am looking for (shown in red).
I am trying to find out if Sumproduct or Countif will provide me the answer but in vain. In the example of the 2 columns of data, how do I find out the number of one-time (or unique) combined occurences for data in column A and B? In my example the answer should be 5. I do not how to proceed with my Sumproduct formula which gives error. =SUMPRODUCT(($A$1:$A$17="A122")*$B$1:$B$17)
View 5 Replies View RelatedI am trying to get two cells to be used to adjust other cells on the same sheet repeated times.
In a inventory sheet I have 5 columns as such , A1 previous balance, B1 Qty received, C1 qty on hand, D1 last cost, E1 current avg cost What I want, is to be able to enter my weekly received items in the B1 Qty received and the new cost in D1 Last cost cells and have them calculate my current average E1 and update my qty on hand C1 total. This in itself is not the biggest challenge. I was asking if there is a way that after the E1 current average cost and C1 qty on hand are updated by that formula, that the next time I enter a new B1 qty received and new cost in D1 Last cost cell they will update again basically without changing the earlier calculations achieved .
Example: Today I have item X with a A1 previous balance of 10 , with a D1 last cost of $1, and E1 current avg cost $1
I want to receive B1 10 more today at $.50 D1 last cost, which ideally would end up showing
A1 previous 10 (or 20 if adding) , C1 Qty on hand 20, D1 Last cost .5, E1 current avg $.75
Next week I want to receive B1 10 more at $.25 D1 last cost , which then would update showing
A1 previous balance 10 (or 30 if adding), Qty on hand 30, Last cost $.25 and E1 current avg $.58
Suppose I have the following data in column A from A1 to A12
Pass
Pass
Pass
Pass
Exceed
Fail
Fail
Exceed
Pass
Pass
Fail
Exceed
I want two formulas one to count the how many times the following pattern of data have been repeated in the range:
1- Exceed after Pass
2- Exceed before pass
i want a function that tell me how many duplicates are in the ID for a particular text.
View 9 Replies View RelatedIn colmn A:A i need returned, which numbers (if any) are repeated and how often each repeated number occured.
Admitedly... I'm lost on this one.
in writing a formula that will count the number of times
the store is listed (Column B) when it matches with closed (Column C).
On the table listed below I will return the data using a match.
From this table
A B C
1/8/2006 9:45Store 1Closed
1/8/2006 9:57Store 2Closed
1/8/2006 10:05Store 3Closed
1/8/2006 10:09Store 4Closed
1/8/2006 10:15Store 5Closed
1/8/2006 10:24Store 1Closed
1/8/2006 10:36Store 2In Progress
1/8/2006 10:41Store 3In Progress
1/8/2006 10:50Store 4Closed
1/8/2006 10:58Store 5Closed
1/8/2006 10:59Store 1Closed
1/8/2006 11:15Store 2Closed
1/8/2006 11:22Store 3In Progress
1/8/2006 11:24Store 4In Progress
1/8/2006 11:33Store 5Closed
1/8/2006 11:51Store 1Closed
1/8/2006 11:56Store 2Closed
1/8/2006 11:57Store 3Closed
1/8/2006 12:03Store 4Closed
1/8/2006 12:16Store 5Not Started
1/8/2006 12:23Store 1Closed
1/8/2006 12:28Store 2Closed
1/8/2006 12:57Store 3Closed
To this table
A B C
1/8/2006 9:45Store 15
1/8/2006 9:57Store 24
1/8/2006 10:05Store 33
1/8/2006 10:09Store 43
I thought I found a formula that would work, but it's not working. Each month I have to count the number of service tickets that have arrived between certain time ranges. They want to gauge during what times we seem to get the biggest batch of service requests.
6 am to 10 am
10 am to 5 pm
5 pm to 6 pm
6 pm to 6 am
The format of the cells are:
1:21:19 AM
1:28:08 AM
1:35:48 AM
1:49:19 AM
2:17:02 AM
7:14:38 AM
7:29:12 AM
8:08:28 AM
8:51:48 AM
8:54:19 AM
The formula I tried for 10 am to 5 pm: =COUNTIF(B2:B677,">="&TIME(10,0,0))-COUNTIF(B2:B677,">"&TIME(17,0,0))
It gives a result of 676, and I know from manually counting that there is only 327 cells that have a time between 10 am and 5 pm.
Im using a DDE to auto update my sheet, every time the word BUY appears on the cell T3 I need update a new table with the following data
=name | =number of times the word appeared
Where name is a reference to the value on the cell C3
Every day I pull a report showing a list of agents that committed an infraction. I want to be able to count how many times each agent committed an infraction. How do I do that? I tried Frequency and it did not work, or am I missing something?
View 5 Replies View RelatedI am working on a spreadsheet where I want to count the number of calls to our service desk between specific hours (ie. 6:00 a.m. - 7:00 a.m.) up through 8:00 p.m. I have looked at all the forums and tried all the formulas that seemed to be what I was looking for and it isn't working! I am adding the information to a summary spreadsheet from which I am gathering the information from other sheets in the workbook. This will be an ongoing spreadsheet done weekly for every month.
Examples: I need to know how many calls from these times on a summary sheet.
6:23
6:28
6:59
7:09
7:35
7:38
8:00
8:26
8:34
8:42
8:43
9:02
9:04
9:23
9:27
9:31
10:06
10:23
10:37
10:51
11:05
11:09
11:19
I want to count every time a certain cell changes. For example, if C2 is currently set at August 5, 2007 and I change it to August 12, 2007, then add 1 to cell B2.
View 9 Replies View RelatedAttempting to do a countif as follows
Count the number of times that F2:F65536=Q1 where E2:E65536 also = N2.
So for a given row, the cell in col F needs to equal Q1 and the cell in col E needs to equal N2 for it to count.
I have a situation in which i have a set of voters ranking performance of others. I need to create a dynamic situation in which as the voter changes the vote it will keep the information updated.
I have attached the data and the stripped down start of what i had. Formulas i can think of to get this done are not working, any combo of functions to get this to work out.
The score represents the total rank given by that voter. I totaled up what participant 5 should be given in terms of points. My design thought was to create a dynamic function that updates the summary page and then use the summary (participant) page to run a pivot.
Voting Results.xlsx‎
My table has one column C with 3 possible values. Column D has either TRUE or FALSE. I am trying to count individually all the times when B = True (F4), T=TRUE (F5) and B/T =TRUE (F6) excluding the blank cells.
But the final goal is to display the total figure required to be answered, but as each question is answered yes or no subtract 1 from the displayed figure. My sumproduct adds up the "B" but does not match with a "TRUE"
I want to count the occurrence of certain letters in a range of cells. In my attachment I need the sum of how many times the letters "C,M,Y,K" occur in the range A2:D2.
View 2 Replies View RelatedI have a problem with LAN function. I have following formula to count how many times appears number 2 in a box: LEN(I5)-LEN(SUBSTITUTE(I5,"2",""))
in I5 I have 1,2,3,12,34,22,21 . Outcome is 5 which is not what I need. What I wanted to do is to get output of how many times appears number 2, not how many times appears expression 2 (it counts also 22, 21, 12, 2) . The output that I need should be 1 since number 2 appears just once in the box.
I have a list of data, integer values ranging from 0 to 36. Imagine a Roulette wheel. The list is long, with over one hundred data points. I would like to view the first 14 data points, and count how many times each value occurs. How many 0's? How many 1's? How many 2's?...How many 36's?
Obviously, many values will have 0 occurrences. Most will have 1 occurrence, some 2, and maybe one or two will have 3 occurrences. I doubt we will see a value with 4 or more occurrences, but it is possible. With this accomplished, I will then note the results. So, that accomplishes the first 14 data points, call them 1-14.
Then, I want to move down the list 1 data point, and repeat the process. So, here I am looking at data points 2-15. Basically, it's the same set of data, with the first point missing, and a new point added on. I will then note the results. I want to continue doing this until the last 14 are viewed.
I want to count every time a certain cell changes. For example, if C2 is currently set at August 5, 2007 and I change it to August 12, 2007, then add 1 to cell B2.
View 9 Replies View RelatedI have a column A with names (let's say that we have four names: A, B, C, D) and a column B with dates.
I need a formula to count how many times appears a name in a column, for every single day (because in a single day a name may appear more than once).
Is this possible with a formula or I need to think at VB?
I have a column with over 60,000 rows of data. I need to find out which value in the table occurs the maximum number of times, and its count.
The traditional methods of COUNTIF or INDEX/MATCH using MODE dont seem to work and excel crashes after a few mins.
Is there any other way to do the same (other than splitting the file into several smaller files)?
I have a list of names all in one column, separated by row...
Example...
John Doe
John Doe
Jane Doe
Jane Doe
Jane Doe
James Jones
James Jones
I want to count how many times each name appears. Like this:
John Doe | 2
Jane Doe | 3
James Jones | 2
This is a very large list of names and I prefer not to have to type each single name into a formula because there are hundreds of separate names.