I have data in table which look like this
A
B
1
NAME
AV
2
SONA
18
[Code] .........
I Want a formula that will enable excel to give me the number of students with Av.
>= 18
>= 15 but not > than 18
>= 10 but not > 15
I have a range of cells that may contain several different abbreviations (text groups). I have used the COUNTIF function with * place in front of text to count cells that contain the listed text eg =(COUNTIF(G6:G39,"*HWD")) which has worked well and returns an accurate count of the cells that contain HWD. I have tried to do the same thing for a COUNT function that contains an IF function and no count is returned. When I remove the * from the formula a count is returned for cells that contain only the HWD text and not cells that contain HWD and other text. {=COUNT(IF(P9:P69="MCAW",IF(G9:G69="HWD",J9:J69)))}.
View 3 Replies View RelatedI am trying to create a formula that will count days since an incident. Column A will have each day of the year in it Column 2, I would like to have 0 in it for each day In the event of an incident, I will replace the 0 with a 1 - but this should only happen on the date, rather than be maintained daily, if you take my point. I would like it to return a value based on todays date, counting the days in between today, and the last 1 entered. It is to cover the whole year.
View 5 Replies View RelatedHaving trouble getting a formula to work that will lookup a site number and count the number of times the site has a particular type of visit. For instance:
Site # Visit
1001 Active
1001 Active
1001 Discont.
1001 Screen Fail
1001 Active
1002 Discont.
1002 Active
1002 Screen Fail
1002 Active
I want a formula that will return the number of active instances for each site, like:
Site # # of Active Visits
1001 3
1002 2
I have the following list
Column A Column B
Name of Parts Amount
A 4500
B 5500
C 8000
D 12000
E 4300
F 28000
G 1000
H 7000
I 10000
J 5000
K 11000
Total No. 11
I want the result as follow.
Slab No. of Counts
0 to 5000 4
5001 to 10000 4
10001 and Above 3
Total 11
We have one excel file for monitoring of action items generated by the management after the study. As since there were around 3000+ rows has been generated since in the beginning of 1990's till to-date. So I was thinking of instead of getting the result through filter manually, I want to create a formula that will count of how many has been closed this year and this month out of the total numbers of action items.
Is it possible to use the COUNTIF function formula to count the number of items in column A, and date of column B, and closed in column C.
In below, we can see that there were 4 items under Revalidation has been closed this month and the total number of closed this year is 6.
TYPE
MTD Closing Date
Status[code]......
I have a worksheet. I would like to count unique number of "Trans" in column A only if value of cells in column B "Type" equals "Return". In example below, I would want to see the value "3" as total.
Trans Type
1 Return
2 Return
2 Return
3 Exch
4 Exch
5 Return
5 Return
I am trying to use the DCOUNT function to count based on two different criteria in adjacent columns is this applicable.
Situation:
I have column B that contains initials and I have column C that contains a type (of store) so it'd look like this
jp RET
jp RTO
jb RET
ma RTO
Rn CI
ma Fur
tc CI
ma RET
jp RET
and I want to have a count of how many RET's there are for Initials JP ect
I want to count from each cell that doesn't contain "0". So if cell C2=100, I want to be able to count the number g1*2 from that cell and return a value. But then I want to start another count from c5 to the number of g1*2 and then another count from c8 etc basically any cell that contains a value other than "0", I want to start a count from.
The point of this is that the half life will expire after that count, so I want to be able to add the drug levels on an ongoing basis until the count of the half life has been reached. But there will be further dosing along the way before this half life is reached and these values need to be added to the existing value until the half life expires.
I am trying to count data using several criteria
1.Need to add data from for a certain category, say "blue" + data during a certain date but exclue the ones with zeros
My formula using arrays look like this, but it is still counting data with zero in the cell as an item
=count(if(A4:A400(text by category)=”blue”,count(if(O4:O400(date)<”04/01/07”,count(An4:An400)[Actual data],-(countif(An4:An400,0))))))
The Data that I have is Grades:
Gade 1/Class A
Grade 2/Class B
Grade 3
Etc..
I wanted to use if function, but I couldn't as there's a limit on the characters that can be used in "IF Function", but unfortunately, I can't because I exceeded the limit, as I have too many grades to include in "Logical_Test",
What I am looking for if one of this data was found write 45 Days, if other grades that is not listed was found write 30 Days.
I have 5 columns of numbers and at the bottom of each column I want to list every number in order from least to greatest and how many times it occurred in the column.
I'll just provide you with 5 rows of numbers so I don't waste a ton of space.
0628313841
0814364045
0203193042
0105101518
2021233335
I have some code which copies down a formula in a range of data. After this I want it to sort the data by this column in question. However my data range will differ in rows all the time and if I record the sort through the macro method it saves the VB code as a fixed range i.e:
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A1967" _
), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
The formula code beforehand does acquire the last row number if that's of any help:
Dim rng As Range
Set rng = Range(Cells(ActiveCell.Row, 1), [A50000].End(xlUp)).Offset(0, 1)
rng.FormulaR1C1 = "=IF(RIGHT(RC[-1],2)=""XP"",""5"","""")"
In a column I have loads of numbers like this except there are 5000 of them.
3
2
0
0
0
0
2
0
3
0
0
2
0
9
how could i sort it into another column so it stays in the same order but counts the 0 values so it would look something like this.
3
2
0 (x4)
2
0 (x1)
3
0 (x2)
2
0 (x1)
9
I would like to have the duplicate numbers in position one ( cell D9:D18), position two (cell E9:E18) and position three (cell F9:18) all counted and then ranked based upon date range in column A.
The number order of precendence is 0-9 with zero being the lowest.
Column (I9:I18) counts the duplicates, draws and no draws of digits 0-9. Column (J9:J18) converts results from column I9:18 into a percent.
I need to add a sort button at the bottom of position 1 (columns H & I 22), position 2 (columns L & M 22) and position 3 (columns P & Q 22)
When the sort button is selected it will sort the data by # of repeats, draws, and no draws as illustrated in (columns I, M & Q) or sort data by Position number from 0-9 as illustrated in (columns H) which will then show data of repeats and percentages out of rank order, because it is in order of number precedence.
Last but not least a graphic bar chart that shows the results ranked highest to lowest from when the sort button is selected with the number at the top of the bar colored black. Bar graph colors to be: top 3 red, middle 4 yellow and bottom 3 blue. This should show a vertical cascade effect.
I have a table with a number of columns of data.
Column A cells have a text string that has a number at the start of the line.
The table is constantly adding additional results (rows) every 5 minutes or so.
Can I dynamically identify rows via the number at the start of column A and copy all cells in that row to a different worksheet?
Ive written the macro below to sort Column A according to the length of the cell values in Ascending order. Ive done this by writing the length to Column B for each value and then sorting on column B. Surely there is a more elegant method, perhaps using arrays.
Sub SortIt()
Range("A1").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Value = Len(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending
End Sub
I have provided an attachment. what I am trying to accomplish. I am trying to have a worksheet that if I input multiple 3 number combinations into the input cell range, after pressing the sort button, it would then sort, rank and count each 3 number combination for me. So as my attached file illustrates, the input cells would be A9:D14. In this sample the ranking consists of cells A19 - A31 as the ranking columns. Cells F19 - F31 show the counted and sorted results and are ranked accordingly. I need a sort button as illustrated in cell F10 to make the worksheet function after the 3number combinations are inputted in cells A9:D14. How do I get started to make this work? I do not know VBA codes or macros so I will need guidance along the way if this is what is needed. I do have some working knowledge of formulas (e.g. countif, rank, etc.)
View 8 Replies View Related-Problem on all workbook tabs
-Problem persists when port of worksheet data is copied to new workbook.
-New workbook with newly typed data will allow sort.
-Can't get my existing workbook sort to work.
I have a vertical list where every other cell is coloured grey. The values contained in the list are regularly sorted into alphabetical order with new values being added and other being removed.
Is there a way to keep the cells alternate grey & white after I have sorted them? Currently, when I sort, if a cell is coloured grey and is moved down one cell the colour is moved and not just the values.
I have a report I produce where I take an XML file, sort it and run some simple formulas then sort another way and run a few more formulas then update a pivot table. The XML files I'm using have the same headers but vary in length. When I set the macro up for a particular XML file everything works fine. When I import another one (different size) I get the error message:
Run-time error ‘1004’:
Sort method of Range class failed
When I look at the code I see that the range is fixed to the XML file I used when setting the macro up. Is there a way to auto-select the table in the macro so I can run the same macro on any size XML?
In the RANK() function I can rank a value depending on one set of values (one condition), but when you SORT data you can choose a second (and a third) condition.
Is there a way to RANK with two sets of values (two conditions)?
I have a string that I need to sort.
Below code works beautifully but doesn't sort numbers, any advice to get this to sort numbers as well?
Function Alphabetize(ByVal sText As String) As String
Dim sWords() As String, sTemp As String
Dim i As Long, j As Long, n As Long
'-- clean up text
For i = 1 To Len(sText)
Select Case Mid$(sText, i, 1)
Case " ", "a" To "z", "A" To "Z"
Case Else: Mid$(sText, i, 1) = " "
End Select
Next
'-- remove leading and trailing spaces....................
How can I sort two columns of function results using the result value? Excel is trying to sort on the function in the cells rather than the results themselves.
View 9 Replies View RelatedI have 3 columns of data: col. A = name (random order), col. B = Net #, Col. C = Gross #.
I am using =LARGE(C$1:C$4466,ROWS($D$1:D1) to Automatically sort col C in decending order.
I would Like to do another decending sort but only the values in Col C that corespond to a particular name in Col A. Can I imbed a index match function combination within the large function to do this?
Name
Net
Gross
All Sort
SortA
SortB
SortC
SortD
A
508
-200.129
101.685
[Code] ..........
I am working on a simple employee list with names, phone numbers, employee ID numbers, etc. I have created a VBA form to make new entries or change existing entries. The form has 'next', 'previous', and 'close' command buttons that work well, but I would like to have my data sort by employee name after any of the buttons are clicked.
My data is in worksheet "Employees" with employee names in column A beginning on row 2. Here is what I have entered for the 'Next' command button:
I'm currently working on a problem that I'm having with Hyperlinks. Basically I want to copy the destination data that the hyperlink points to onto another sheet.
The main problem I'm having here is that I cant seem to find a suitable method to do this....I had the idea of using the hyperlink.follow function and then copying the data once I'm there and then moving back to the source hyperlink. My only problem with that is that I dont know how to find the cell address that the hyperlink is in, once I have found that hyperlink.
Therefore my question is two-fold:
- Is there a way to copy the destination data using some sort of hyperlink function?
- If not, is there a way to find the cell address within a worksheet, once a hyperlink is found on that worksheet?
I've attached the spreadsheet below to give a better idea of what Im doing, along with the "findHyperlinks" macro,within that workbook, that I am currently working on.
I have following data to sort/filter
Sector
Flt no
origin
[Code]...
Is this possible with excel functions?
Essentially i get a Runtime 5 error on the bold bit of code....
I am trying to define a range .... by using thexlUp function find the last row ( which works ) i then pass this variable into the sort code and get the error its probably very simple to fix, all it does i sort columnA but finds the last cell .... instead of the whole column,,,,
Sub Macro7()
'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+e
NumberOfRow = Sheets("Sheet1").Range("A3000").End(xlUp).Row
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Sheets("Sheet1").Range("A2", Cells(NumberOfRow)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
[Code] .......
I need to create a custom function that can do the following without modifying the selected range. If I have data in a row, I need a function that can give me the following result:
Original unsorted data:
79
45
23
56
1
The custom formula will not modify the selected range, and after saving it or copying somewhere and sorting it, it will provide the following result: if N or count(range)=5 , and p=1, average(second and third smallest values of the range)=34; and if N=4 (lets say that we errase the number 1 data point in the range), average(third and fourth smallest values of the range)=67.5 So it is basically a sort and then getting the averages of specified row numbers depending on N or count(range) Here is what I have.
I'm missing how to create a variable with the sorted range and how to use an index to refer to the rows I want to average:
Function CSORT(r, p)
Application.Volatile
'rs or something similar that will contain the sorted range r
Dim rs As Range
'Sort rs (The following line one doesn't work, so I need one that does)
rs = Application.Range(r).Sort
Dim p As Integer
[code]....