I use this formula to count uniques in Column I if they started with "P" :
=SUMPRODUCT(($I$2:I554"")/COUNTIF($I$2:I554,$I$2:I554&""),N(LEFT($I$2:I554,1)="P"))
Now if I add 2 more criteria it gives a wrong result" :
=SUMPRODUCT(($I$2:I554"")/COUNTIF($I$2:I554,$I$2:I554&""),N(LEFT($I$2:I554,1)="P"),N($F$2:F554=F555),--($G$2:G554""))
as 0.0625
I want something to give me the total of different names in column A, I know the total is 8 but if I add another name I want Excel to add it up automatically in cell A2 in sheet #2.
View 9 Replies View RelatedI have about 160 rows in collumn A. I want Excel to count them, but ignore blank ones and repeated ones. How can I accomplish this using macro?
View 5 Replies View RelatedI have attached a sample sheet. Can someone be kind enough to teach me how to count unique numbers of visible cells ( I would be applying filter in the table) in textbox 1; and subtotal of the visible list in textbox 2?
Right now the range to count unique numbers is set to A2:A15, but I want to be able to change that range to other ranges, like B2:b100, etc. I do not know VB.
I have a list of names in a column that will be changing on a weekly basis (with some names repeating). I need to create a Named Range that only counts unique values from that list of names (dynamically changing every week).
View 11 Replies View RelatedI am designing a userform that involves a listbox that I am having trouble with. Please note I have little experience with vba, so I don't understand most codes.
I want to make a mulitcolumn listbox, populated with the attached pivot table. I really do not know where to start, and there is no "populating multicolumn listboxes for beginners" instructions anywhere on google or in here. At least, none that I understand.
What I am trying to create is a 2-column listbox, where in the first column, the "code" fields are shown, and in the second column, the corresponding " Name" fields are shown. In the pivot table, you can see how several routes fall under a service, and several services fall under a pc, etc.
I want to count cells in column AA that are graeter than 160, and in column N = "RM" and in column A = "CBP". Can't seem to get this right.
View 4 Replies View RelatedI have a file where I want to count number of cells where the value is greater than 0. in the attachment, i just want to populate that count below the column indicated therein. So in the example, desired result is two.
View 4 Replies View RelatedI have a formula that counts if a date range is present. However I need to change it to count another column only if that date range is present. For example a17 a50000 the user will enter the date of the order. and in column B has the order number. I want the formula to count the order numbers for a data range in column A.
Here is what I have but it is counting the dates in col A not the order numbers in B?
I have a formula that tests the minimum time in a column. If the time is the minimum it gets a PB (Personal Best) notation. My problem is once a season there is a competion that needs to be recorded on the worksheet however I do not want this time for this competion counted in the MIN test or to get a PB allocated. I have worked out how to ignore the PB but I cannot get the MIN test to work properly. Dates of comp are in Col B, data (times) in Col C and formula for PB's in Col D. When a cell in Col B (Dates of comp) = Region I want the MIN test to ignore the value in the adjacent cell in Col C.
View 14 Replies View Relatedproblem is in the same vain as my last thread "Formula to count MIN with condition". In the attached worksheet samples, Col B has competition dates, Col C is time/distance data, Col C (filled down) is the formula that recogises if the time/distance is a PB.
Once a season there is an event "REGION" where I want the formula to ignore the data in Col C adjacent to "REGION". The date of REGION changes every year and could be anywhere in Col B. The first attachment is what the sheet currently looks like and the second is what I want it to record, specifically D11. Even though D10 is a better distance, it would be ignored as it is REGION.
I'm a novice trying to figure out the following:
I have a column, where each cell in the column has one of the following "ratings" entered as text:
excellent
good
fair
poor
How do I count the number of times "poor" AND "fair" are listed in the column? I used the following countif forumla to count the # of occurences of the word "poor":
=COUNTIF(SurveyData1stRoundSortedbyDoc!Y2:Y30,"poor")
...but how do I adjust the formula so that it counts the # of occurences of the word "fair" AS WELL?
I have 2 columns in my spreadsheet:
B:B is a column of dates.
C:C is a list of names
formula that will count the number of times the name 'SIMON' appears in column C:C but here is the catch: I only want to know how many times that name has appeared over the course of the previous week. IE NOW - 7days
I'm trying to sift through 10000+ rows of results, and I'd like to know if there's a count formula to determine how many times I have matches in one column (name) and mismatches in another column (ID #)
Profile ID |Name | License #
12345 |Debra Nelson |12345678
12345 |Debra Nelson |23456789
I want to count how many times theres a match in the 1st or 2nd column (profile ID, name - those two columns should match) and a mismatch in the 3rd column (License #). I'm not sure if this can be done with a formula, like a COUNTIF, or not.
I have many names of people in column A .. what formula do i use to count all the names in the range in coumn A?
View 2 Replies View RelatedI need a formula that will:
Count unique records in column C
Where value in column N = "ABC"
And value in column I = "XYX"
I'm trying to count the number of incidents in column BB that are >0 but only IF the value in column E is "Abbeywood". i.e. how many times there's a figure greater than 0 for Abbeywood. I can't seem to get count if to do this!
View 4 Replies View RelatedI am trying to use COUNTIF formula to count how many item in a column that meet certain criteria, say between 10 and 20...
=COUNTIF(G1:G100,"AND(>10,<20)")
I'm trying to write a formula that will count the number of unique occurrences in a column, if a specified value is found in a different column.
So I want to count the number of unique values in the "ID" column if let's say the text "NameA" appears in the "Name" column.
ID Name 12345
NameA
NameB
NameA 12346
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'm trying to figure out how to count the number of unique countries where there is an office.
So pretty much, I want to count the unique entries in Column A where there is an X in Column C.
I would like to sum uniques across worksheets. Below are the formulas that Domenic provided me and it works great but I could not modify the formula below that is in red to sum uniques instead.
=SUMPRODUCT(--(RIGHT(A3:A37,5)<>"Total"),--(MATCH(C3:C37&D3:D37,C3:C37&D3:D37,0)=ROW(INDEX(C3:C37,0))-ROW(A3)+1),B3:B37)
Define Names:
ArrayA:
=COUNTIF(OFFSET(INDIRECT("'"&Sheet1!$C$2:$D$2&"'!A3:A100"),ROW(INDIRECT("3:100"))-3,,1),"<>*")
ArrayB:
=N(OFFSET(INDIRECT("'"&Sheet1!$C$2:$D$2&"'!"&Col),ROW(INDIRECT("3:100"))-3,,1))
Col:
=CHOOSE(Pos,"A3:A100","B3:B100","C3:C100","D3:D100")
Pos:
=TRANSPOSE(MMULT((T(OFFSET(INDIRECT("'"&TRANSPOSE(Sheet1!$C$2:$E$2)&"'!B1:D1"),,COLUMN(INDIRECT("B:D"))-COLUMN(INDIRECT("D:D")),,1))="Net")*(COLUMN(INDIRECT("B:D"))-COLUMN(INDIRECT("B:B"))+1),TRANSPOSE(COLUMN(INDIRECT("B:D"))^0)))
=SUM(IF(ArrayA,ArrayB))
looking for a way to look in a column for a value, and then spit out the highest value in corresponding cells in a column further over
so i want to look at all the classes associated with the same case, and have excel spit out the highest CscR no associated with those cells that do match..
yes i could do it manually, but my actual file has like 4000 samples in the first column.
Once I have used this code to filter for unique item(s)
Sheets("Source Data"). Range("Data").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("Source Data").Range( _
'"Criteria"), Unique:=True
Sheets("Source Data").ShowAllData
As this filter searches for unique value there is only 1 row of info. I then want to select specific (1 or 2 cells) info from this row and the make this cell(s) = to a cell on another worksheet. As this unique row might be 199; I don't know how to reference it using VBA. I know what columns I need but cannot determine which row it will be in. Assist as I have looked at various threads w.r.t. duplicates/autofilter/search but cannot seem to get the correct code.
I have this formula below it will list uniques from column A but I would like it return in a alphabetical order. Is there a way to add that condition in the formula below a all in one formula?
=INDEX($A$3:$A$23,MATCH(TRUE,ISNA(MATCH($A$3:$A$23,C$2:C2,0)),0))
Example:...
I would like to get a unique list of letters from multiple columns.
I tried to use this formula below but it does not work with multiple columns....
I have a list with many duplicate names in it, from which I only wanted 1 instance of each name, to be placed in another sheet. So, I recorded a macro that selects, from the sheet called "Data", column E (the column with the relevant list), copy/paste's it into sheet 3 in column A, selects the new column, and runs an Advanced Filter command on it, to identify and display all the unique names in it in the same place. It ran just fine when I recorded it. But then, when I deleted the data created by the macro and tried to re-run it, I got random data. I found that whenever I click on a different cell, it provides a different output. I only want it to select Column E, and copy paste that, not other random columns depending on which cell is currently selected. This is my
Sub Fetch_Unique_Names()
Sheets("Data").Select
ActiveCell.Range("A1").Select
ActiveCell.Range("E1:E2200").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Range("A1:A2176").AdvancedFilter Action:=xlFilterInPlace, Unique _
:=True
End Sub
formula to count two criteria in the one column if there is a third criteria in another column. i.e. if column B = either "A" or "B" and column C="D". I have tried the countifs function without success.
View 4 Replies View RelatedIs there a way to do this without using a macro, but I need it to be in a macro.
Column A has a value I am calling a label, ex. ABCDEF which occurs over and over. Column B has a list of animals, many of which repeat AND will be together if they do repeat. In other words, all rows in Column B with Cows are together, occurring in consecutive rows. I need a macro that will look at each row in column C and increment +1 starting at 0. That will be concatenated with the value in Column A and pasted as a value in column C.
See the linked spreadsheet tabs for Before Macro and how it should look After Macro is run.
[URL] ........
im trying to count all the cells with data in sheet 1 column g but it must omit any cells that have "vs" in it. all cells have scores in like 1-1 2-2 2-1 etc but a few have vs in them and i dont want them counted
View 5 Replies View Related
