I have a column of race results in A:A, ie (1,2,3,4,5 etc placings) and need to count the highest frequency of 'outs' not wins (wins being #1 ) between wins, and also the highest frequency of wins in succession.
I have a list of 267 individuals who are each in one of 4 class designations (A,B,C, & D). They produce widgets for me. The individuals’ names are in A2:A268, their class designation is in B2:B268; and the number of widgets they each produced is in C2:C268. Row 1 is a header row.
I used the FREQUENCY function, for the first time, to show how many of the individuals produced 1-10, 11-20, 21-30, 31-40, 41-50, 51-60, and 60+ widgets last year. NOW, I’d like to take it a step further and show the number of individuals that produced widgets in each of these number groupings, but want it further split out by their A, B, C, D, class designation. So, I want to see how many individuals in the “A” class that produced 1-10, 11-20, 21-30, etc. widgets; and how many individuals in the “B” class produced those numbers, etc.
I’ve been playing around with using the COUNT and IF functions together and the FREQUENCY and IF functions, but have not been successful. Am I making this more complicated than it is? By the way, their class designation doesn’t have any relation to the number of widgets they produced, and the data is sorted alphabetically by the individuals’ names.
I used Match array to count the frequency of occurences (ie. string begins ASC) with but to no avail. Match or Frequency function how to do this? =MATCH(IF(LEFT(A1:A9,3)="ASC",LEFT(A1:A9,3)),A1:A9,0)
I have a grid of random numbers. What I want to do is make a new, corresponding table that has those numbers' frequencies from the initial dataset.
So, if I have a row 1,2,3,4,5, the first row in my frequency table shows the frequencies for those numbers, whatever they might be regarding the entire set of numbers.
I'm just wondering if there's a more automated way of doing this than running the historgram function and then manually matching the frequencies with the original numbers to come up with my table. My dataset is growing, so that's becoming too time-consuming.
I have a list, 50000 rows long with phone numbers and the service provider it belongs to. I want to extract some of this information. I'm looking for a formula which can give me the following: How many unique phone numbers is there in column B from service provider number 4 and 5? The following pic is just an example of how the list is compiled. How can I get sum up only the numbers from 4 and 5 and not the whole bunch? If I use this formula
Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas =SUM(N(FREQUENCY(D1:D51112,D1:D51112)>0)). I sum up all the unique phone numbers, but I don't know how to set a "if service provider is 4 or 5" if you get what I mean. This was the first part. The second part is finding out how many times each unique number from service provider 4 and 5 is listed.
I've tried to adapt a formula I've found in the forums. But the result comes back at a figure much lower than I expect. I have a column of numbers (column J)that I would like the frequency of but only if the date contained in another column (column R) is beyond a certain date.
On my spreadsheet I have multiple rows and cells containing alphanumeric text (text is a combination of a person's name and goals scored) i.e. Holland1 appears in row 3 cell P3 then Holland3 appears in row 6 cell O6.
What I would like to be able to do is have a forumla that looks at every row and cell in my range and sum the numbers of the alphnumeric text if the cell contains a certain name i.e Holland.
I had tried to use the array formula =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$26),1)),0),COUNT(1*MID(A1,ROW($1:$26),1))) which I found on the internet but this only works for one cell and doesn't allow for a set criteria to be met.
I hope someone can help me with adding an additional criterion to the formula below, which works and provides an accurate answer. The array formula is:
Where Column O has either a date or is blank. Where Column B has a unique ID number which occurs more than once.
What I need to add is one more criteria from Sheet1, Where Column P has a unique names which occurs more than once.
I tried two changes: 1. Adding an additional IF statement as follows: SUM(IF(FREQUENCY(IF('Sheet1'!$P$5:$P$315=A$8,IF('Sheet1'!$O$5:$O$315="", ... Which I couldn't get to take; and 2. Adding a IF AND statement as follows: SUM(IF(FREQUENCY(IF(AND('Sheet1'!$P$5:$P$315=A$8,'Sheet1'!$O$5:$O$315=""), ... Which took but gives me a #VALUE error.
I have a list of names in C1 down that I want to count. There could be hundreds of names in C1 and I don't want to sumif all of them. There must be a simpler solution
I have a signup record that expected to grow up to row 400. On the top, there's a monthly tally for reports. Column B to count the number of boxes taken and Column C for the users picked up the boxes.
Users should be accounted as one appearence in a month, regardless re-appearance in the same month or different month.
I haven't found the generic formula to uniquely count the number of users regardless their frequency in a month.
NOTE: Timings, box numbering format and usenames are randomly generated. Any similarities with real subjects will be coincidence only.
I have a frequency list (frequency per year) resulted from previous calculation. What I want is to generate random number as many as the frequency in the specified year, so that every year I will get the same amount of random numbers as the frequency. It is kind of the opposite of FREQUENCY function (but with random numbers involved). I need to use the result for further calculation.
I need to get a general formula which if possible, automatically calculates the frequency of unique texts (no duplicates) under a particular month (January) without manually inserting the cells which is under january. If possible, the formula will also check to see if the company is either Cash or Cheque.
I have three columns; Date, Company name and Payment mode.
Date ranges from 1 jan 2010 to 31 dec 2012. Cell A2 to A234
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.
We have a few residential care homes across UK. I have a workbook where I store all our residents fees which I update daily. There are 2 worksheets on one I store list of residents on the other one I have a daily breakdown.
What I'd like to do is to use worksheet "daily" to sum fees from "all residents" with criteria like: - carehome ("daily" row 2), - fee ("daily" F:K,N:S,V:AA etc) - date ("daily" C7:C1073).
While fees are on worksheet "all residents" where: - column A has carehome name on it - column M to AG has fees on it - row M3:AG3 has dates on it
For example id like to count in cell F7 how many residents in Branch Court "Bc" were in residency on 12 March 2012 with £520 fee looking on worksheet "all residents" with criteria fee, carehome,date)
Each time one of the council backdates fees or changes a resident to lets say a private one I have to manually change all the numbers which takes time.
Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.
I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
I believe I need a loop code to do what I need, because none of the functions I've tried have worked. I want to start at B2 and go down the column until I come to a YES. When I find a YES, I want to know the number of NOs that preceded it. Then I want to go from that YES(#1) to the next YES(#2) and count the number of NOs between YES(#1) and YES(#2) and so forth, until I run out of rows. For example, in C5, the answer is 2, because there are 2 NOs between YES#1 and YES#2 in coulmn B, and a 1 in C7, because there is 1 NO between YES(#2) and YES(#3) in column B.
I have a spreadsheet which lists letters issued, the date issued and the potcode. I need a formula that counts, on a weekly basis, the number of letters issued to postcodes in Sutton and Bexley.
I have a count sheet set up on one worksheet and the list of postcodes applicable to each area are listed on another worksheet. I have been playing around with sumif, sumproduct etc, but these don't seem to work as I am pointing the formula to a list of postcodes and not an individual postcode. For example, the formula for one week needs to tell me, the number of letters issed to Sutton between 26/07/08 - 01/08/08. This is what I tried -
But I get #N/A - if I change the last refernce to a specific cell instead of a range it works, but this will make the process very lengthy as there are lots of postcodes!
I have this kind of data going down about 2000 rows. I want to find how many times 1 and 5 appear in each row until there is no more data to read. I found the formula below but I cant use it for 2000 lines plus any extra that will appear in the future...
I have a field that contains the following: 012100002030
I need a formula that will tell me whether or not the string contains a number higher than 2 or whether or not it contains more than one 2.
Examples:
001000002011 111111000022 401110000000
the first loan would not meet the criteria as it contains no number higher than 2 and only contains one 2. the second loan would meet the criteria as it contains two 2's. the 3rd loan would meet the criteria as it contains a number higher than 2.
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.
Problem I was having in peforming a 'Count & Sum' routine for records which met certain criteria.
Unfortunately I wasn't able progress this any further, and I take full responsibility for this, because I caused issues by incorrectly specifying the criteria in my initial post.
What I'm trying to achieve is a follows:
Search the range "ADPeriod" (Which is column J on the "All Data" sheet)for the value shown in cell in B2 on the "Expected Outcome" sheet, andSearch the range "ADPLOB"(Which is column B on the "All Data" sheet), for the value in the rows of column B on the "Expected Outcome" sheet,andSearch the range "ADJRole" (Which is column I on the "All Data" sheet) for the value in row 2 on the "Expected Outcome Sheet"Where all three are found,Count the number of unique values in the range "ADSName" (Which is column D on the "All Data" sheet) and enter the value in the applicable rows in column C of the table on the "Expected Outcome" sheet, thenSum the associated value in the range "ADAFTE" (Which is column N on the "All Data" sheet), and place in the respective row in column D of the table on the "Expected Outcome" sheet.
This is the link to the file: [URL] .....
As before I will be adding further values in row C of the table on the "Expected Outcome" sheet, but I just wondered whether someone may be able to show me how I may be able start which I can build upon.
I have a dataset with odds for soccermatches, and results. I want to count each time a specified odds has won the match, but i cannot find out which formula to use.
For example:
HTeam ATeam Res H-Odds D-Odds A-Odds 1.Team1 Team2 H 1.60 2.10 4.30 2.Team1 Team2 D 1.64 2.30 3.30 3.Team1 Team2 H 1.60 2.60 4.35 4.Team1 Team2 A 3.10 2.80 1.60 5.Team1 Team2 A 1.60 2.10 3.30 6.Team1 Team2 D 1.60 1.60 4.30
HTeam= Home team ATeam= Away team Res = Result, Home, Draw, or Away H-Odds= Home odds D-Odds= Draw odds A-Odds= Away odds
Defining my problem: I want to count the times the odds; 1.60 was the winning odds. I imagine i need to set up 3 columns: Homewins, Draws, Awaywins.
Kind of like: =COUNTIF(A1:A50,"1.60"),IF(B1:B50,"H") like that, only that is not correct