I need a unique count of the ID number. What I need is to check and see if the row has a number in it and if it is greater than 0 and is not blank. If the row has 0 in it, then that is not counted in the count. For in stance .....
View 9 RepliesCan someone please help me with formulas to calculate the # of Unique Customers, #of Items, and Amount for Q1, Q2, Q3 and Q4 for the year in A2 (which contains a drop-down list).
Please see attached sample workbook. (I can’t figure out how to combine the formulas for count unique items with a specific quarter.)
I've an excel sheet with 1000 rows data. I need a formula to count number of unique items in a column with a condition. Please find a sample data sheet.
I've tracking list that has ordered and delivered items for a given month on weekliy basis such as Week1(W1), Week2(W2), etc. I need a formula to count number of unique item delivered on this month.
Item 1 to item 8 delivered and item 10 also delivered in week 4, So totally I need to get the result as 9.
I was trying to use "countif" for validating and counting each row, it's throwing value! Error =1/(COUNTIFS(A:A,A2, c2:c23, ""&0)) i.e., = 1/countifs(unique items from column A, then nonblank cells in column C) but it's giving Value error..
I just need to count the unique item with another condition.
Item details
Order week
Delivered Week
Item 1
W1
W1
Item 2
W1
W1
[code]....
I would like to check if there's a way to count the number of unique value with a given condition
Eg. condition where parcel count <> 0, also want to find out the unique value of district and postal sector
Attached is the files, and my desired result is highlighted in RED.
I'm looking to get a formula to counting unique values listed in a column depending on a condition also find the attached file for more details
[URL]
I need to count the amount of unique values in a range, but only count unique values that also have a seperate condition. To illustrate with the 2 column dataset below....
Colour Letter
Yellow A
Yellow A
Green B
Yellow B
Blue A
Blue A
Yellow C
For this set of data i want 3 seperate cells for each 'Letter' telling me that, for 'A', the answer is 2 unique values (i.e. Yellow and Blue). For 'B' there is also 2 unique values (i.e. Green and Yellow) and finally for 'C' there is just 1 unique value (i.e. yellow).
I need to count the number of unique text values in the first column given a condition in the 2nd column. For example, the formula that I'm looking for should give a result of "3" because it is an SME, and there are 3 unique companies that are classified as SMEs.
Company Name
Company Type
Company A
SME
Company B
SME
(I cant seem to make the tables visible but Company A and Company D right below the header should be in one cell) This should still yield a value of "3" despite having Company A and D in the same cell.
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
View 14 Replies View RelatedSo I have one sheet that needs to pull data through to another sheet (which is a stats summary)
I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.
For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.
I've found part of my answer from searching for a previous thread and altering to suit my needs.
I've got the array formula:
=SUM(IF(FREQUENCY(IF(D2:D20000"",MATCH("~"&D2:D20000,D2:D20000&"",0)),ROW(D2:D20000)-ROW(D2)+1),1))
This gives me the correct number of unique entries. What I'm after is the number of unique entries that start with 1042
I need to count all unique entries in column A (list of names) (this bit I can do!)...however:
Column B gives each name a value of A or B, I need to count all unique names with an A and all unique entries with a B.
eg
Matt A
Matt A
Matt A
Bob A
Tom B
Tom B
This would give a count of 2 for A's and 1 for B's.
Could someone let me know if my suggested formula below will calculate the number of Unique records that represent either an A or a B.
=SUMPRODUCT((B5:B14="A")/COUNTIF(A5:A14,A5:A14&""))
Although it is giving me the right answer here I am not confident it is correct.
I have a test sheet thus :
+ABCDEFG
1Bob11234
2Sally2
3Ann3
4Emily4
5Lexi3
6Bill2
7Josh1
8Jacob2
9Mary3
10Gary4
11
In D2:D11, I want a non-duplicate list of all the "1" names, i.e., Bob and Josh. In E2:E11, a list of all the "2" names (Sally, Bill & Jacob), and so on. Sorted would be nice, but not necessary. When new names and their numbers are added to A1:B200, they need to automatically show up in D2:G:201 as appropriate. I've looked over VLOOKUP, INDEX, MATCH, etc., but all seem to simply return the first and not every match. Maybe I'm just missing it.
Is this possible without VBA? Not that I have any objection to it, but delving into and learning it is a little outside the available time I have now. If it takes VBA I'll address it when I can free up some time.
Formula which will count unique values in column A based on condition in Column B which "y" .....
Show ranges as A:A instead of A1:A100 as I dont know the size of the table, it can be thousands rows .....
Counting Unique text entries in a sheet with a condition ..
View 14 Replies View RelatedI have a column with different cities that customers visit (Barcelona, Lisbon etc) and another column with their booking requirements (All inclusive, breakfast only etc)
I'd like to set up a count on the total number of customers who are going to Barcelona and having all inclusive.
I've tried using nested if statements with vlookups and a countif but can't seem to get that working.
i've got a list of values that need summing, but only if i certain value is in another cell:
a b
1 paid
1 paid
1 not
1 paid
1 not
i need a formula that will count all the values in column a only if column b is set to "paid"
I need to calculate the lookup value that have more than one...
I want to hav a simple analysis on likert scale
I hav students from 3 (or more) institute (A,B,C). 3 survey questions...
How to calculate how many students form each institute that score 1/2/3/4/5 (base on the likert scale, maybe up to 10) for every single question (in my sample worksheet, i named it as Item1, Item2, Item3 as a sample questionaire)
I need a formula that will return a total for the number of cases that have been closed within 6 months. Also I need a percentage total for number of cases that have been closed within 6 months. So the conditions are that the case has been closed and within 6 months.
I tried to use a IF formula that returned a 1 if within 6 months and a 0 if outside 6 months and then just SUM() the column, however for some reason that just returns 0. I am using Excel2003
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 have data like
col A col B col C col D
DLM2.2 kWDLM75 kWDLM55 kWDLM160 kWDLM11 kWDLM1.5 kWRDLM110 kWRDLM2.2 kWRDLM11 kWRDLM55 kWDLM11 kWDLM30 kWDLM45 kWDLM1.5 kWUS40 kVAUS10 kVAFD45 kVAFD45 kVAFD6.4 kVAFD25 kVAFD45 kVAFD11 kVAFD11 kVAFD150 kVAFD10 kVAFD18.5 kVAFD25 kVAFD18.5 kVA
The problem is col B having M type and col C contain their respective ratings. I want summary report.
Rating 2.2 should give me total count, but if you see there are types.
In col A -
DL
RDL
blank cells
so, 2.2 DL = the perticular count
2.2 RDL = the perticular count
Only M type having subtype DL and RDL.
Im col B, there another subtype also like US or FD. For that also particular rating show their exact count.
Is that possible? I want excel formula not vba code.
Build table with 2 matching condition.
View 6 Replies View Related1. Count of Unique Print Customers
2.Total of Print Revenue
3.Count of Unique Online Customers
4.Total of Online Revenue
5.Count of Customers advertising in both Print and Online
6.Total Online and Print Revenue
I have a spreadsheet with employees performance results for the last 12 months. Column A contains name, columns B to M contain text "Gold", "silver" or "bronze". I need to count how many consecutive months thay have achieved "Gold" status BUT must include latest month.
So, if column M contains "silver" or "bronze", the consecutive answer (column N) would be 0. If col M = "Gold" but L doesn't then N = 1. If M and L = "Gold" then N = 2 etc etc.
Is there a formula I can use in column N for this without doing 12 "if" functions?
I think this needs to be an array formula but the ones I've tried I can't get to work.
I have a spreadsheet which looks like this: http://img46.imagevenue.com/img.php?..._122_521lo.JPG
I'm trying to figure out how to set up a macro which can generate a correlation table for each row. For instance, I would like for the macro to cycle through the row labeled 'List1' and record each cell value (B1, C1, D1, etc). Then, I would like to compare those values to the row labeled 'List2' to determine how closely they correlate. If List1's columns were identical to List2's columns, (order does not matter), I would want those two to have a correlation of 1 or 100%. If none of those two lists' columns match, I would want a correlation of 0 or 0%. In the end, I would like to only keep the List rows which are most uncorrelated with each other.
the report I'm creating has rows of refreshable data with a header and I need to find a way to count number the rows where a "yes" value appears in column J, then paste that total number into another new sheet in cell E9. I saw it on here a few days ago, but didn't mark it
View 4 Replies View RelatedI have cells in a column, which contains text and numbers. I want to count the number of cells that have value greater than zero.
I tried using DCOUNT, but I got no idea why is its not working
=DCOUNT(I15:I114,1,J5)
where the values are form I15 to I114.. and J5 contains the condition >0
Also, while I am at it.. Is there a straightforward way in excel to get the count of the max run of cells with values greater than zero.
for e.g if cells from 18 to 30 have values greater than zero , and cells 44 to 50 have values greater than zero. then I want it to return 13. i.e (30-18)
I am hoping that there is a simpler way than looping through the column in VBA
1.I have a map which fill with value in the cell like shown below.
2.I need to count the value in the cell using VBA.
3.There are many maps with different value that I have to count manually. So it takes time to count the value on by one.
4.By referring to the map, I want the VB to count how many value in each cell within the range of the map only, which mean I just want to count how many 1, 14, 19, 2, 99 and others.
5.The answer will be displayed on the bottom of the map like this:
For example:
bin1=?
bin14=?
bin19=?
bin2=?
bin99=?
bin8=?
Here in the code wbResults is a Workbook present in my present folder. In the Sheet1 and Column "I", the result of test cases is entered. The Result of the TEST CASES can be PASS or FAIL. So i am trying to get the total number of PASS test cases and Total FAILED test cases. BUT with the code i have written, i am not able to fetch the right data. Is the method of comparing wrong? or what else? i have tried to debug but nothing comes out.
Do While Not i = 1008
If (wbResults.Worksheets("Sheet1"). Cells(i, 8) = "PASS") Then
PASS = PASS + 1
End If
i = i +1
Loop