I need a formula to count the total items per unique id. For example
Column A Column B
11111 Basketball
11111 Basketball
11111 Basketball
33333 Baseball
33333 Baseball
22222 Hockey
Output to Column C,
ID 11111=3 Basketball
ID 33333 2 Baseball
ID 22222 1 Hockey
Counting unique items in column (numbers)
View 9 Replies View RelatedCan 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]....
Looking for a formula that will give me a count of unique entries (i.e. duplicate entries to not be counted.)
basically a formula that says look in D4:D4001 and count only one of each entry....do not count the duplicate entries.
I have a Excel sheet and I have put Autofilter for a particular category I choose I need to count number of items in another column(autofilter) , for eg... there are two columns Category , Items When i choose a particular Category I need to count number of Items in the autofilter for that particular category?
View 9 Replies View RelatedAs the subjects states I need to count the unique entries in a filtered range.
View 7 Replies View RelatedA B C D
1 Yes Red Green
2 Yes Blue Black
1 No Blue Black
2 No Blue Green
2 Yes Red Black
I am interested in finding the number of unique entries under column A where column B = Yes; Column C = Red OR Column D = Black (answer is 2)
I need to count the unique for a customer. The areas to be counted are separated by blank lines. At present, I am using sumproduct(1/countif(range1,range1), to count the unique items. This formula works perfectly except that it takes me almost an hour to do this for all the measurements. Is there a macro that can provide me with the same results -- putting the same values where I am presently have the formulas (the cells that before the blank lines)?
View 6 Replies View RelatedThe pivot table has only two columns, the first is the identification number and the second is the count of the identification number. I am trying to get a count of the number of identification numbers, not how many times it was entered in the spreadsheet (some numbers are entered more than once on different days). It currently looks like:
Column A Column B
00000001 1
00000002 1
00000003 2
00000004 1
00000005 3
Grand Total 8
I'm trying to arrive at 5 for the answer, so that each number is only counted once even if used more than once.
I would like a formula to count the number of unique digits from a range of cells.
For example...
Range A1:F1...
11 23 36 47 48 49 = 8
Range A2:F2...
1 11 12 21 30 31 = 4 etc
My company has a catalog of ~6000 unique SKUs that we sell online. Currently we do not have a way to quickly determine how much a given item has sold month over month, and the rate of that change.
I have an excel workbook full of our last year's worth of sales data. I have organized it by placing each month of data in its own worksheet. I would like to be able to create a search box that will allow us to enter in a product ID, and have excel then create a graph with each month's of sales for that particular item. Is that clear?
I'm trying to count how many production orders i have per week. However, there are duplicated production orders per week. I only want to count how many unique orders there are for each week. I only see the ability to "Count", which counts my duplicates as well so it over inflates my true quantity.
View 3 Replies View RelatedI need to count the total number of times 4 different values appear in a column. This formula works for one value:
=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
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 Relatedi am looking for formula in my macbook pro excel where i have purchase apple 50kg for $50 and grapes 20 kg for $30 and orange 10kg for $15 and am doing this daily and in end of month i want to see separate kg's and amount for whole month and later year of apple, grapes and orange without sorting it
View 1 Replies View RelatedI have a code which works perfectly and adds the dates from Column A to UserForm1 ComboBox2.
In column B, I have textdata - UserForm1 ComboBox5.
I'd like to transform the code to add only the dates which correspond (cascading) to 1.1 textdata in ComboBox5 (Column B) and 1.2 dates in ComboBox2 (Column A).
Code: ...
Formula that can calculate % of total for individual items. For example,
Item NoCategorySales% of total
1234Bed150
5678Bed100
1547mattress52
58658mattress188
12356bed12
8954mattress185
I need to find out each item % of total per their own category. For example item 1234 should equal 150/(Total Bed) to get % of total per category and item 1547 should equal 52/(Total of mattress)
I have ~500 rows of data in columns A, B, C that is as follows, for example:.............
I am having trouble with coming up with a formula that will add up the total items shipped for each item. For example, Apples = 62 items shipped.
I am using Excel 200o to create a PivotTable but am having problems wiht the Totals. Is it possible to create a PivotTable that includes hidden items in the Totals (row)? I tried the Subtotal Hidden Page Items option but this seemed to do nothing to the Total or subtotal.
View 3 Replies View RelatedI have been using the wrong formula to count total entries in columns and only just found this error. The MAX formula in cell B4 is: =MAX($B$12:$B$36). If the all the rows are full within range F12:F36, then the MAX formula is fine to count the total within range B12:B36 (25) so I thought. But sometimes there are omissions between F12:F36. If there are 2 blank cells anywhere within F12:F36 for example, then B4 needs to show 23 respectively. In the sample WkBk B4 needs to show 8
View 2 Replies View RelatedHow to make it copy over A, B, and C not just A and B? I tried adjusting some of the ranges, but I get a Run time Error 104. Also, what is the (Rows.Count, 3) doing?
Sub CopyUnique()
Dim LR&, LR2&
Application.ScreenUpdating = False
With Sheets("AY")
.Range("A1:B1").EntireColumn.Insert
LR& = .Cells(Rows.Count, 3).End(xlUp).Row
.Range("C1:D" & LR&).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("A1:B" & LR&), Unique:=True
LR2& = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("A1:B" & LR2&).Copy Sheets("ORG").Range("A1")
Sheets("ORG").Columns("B:B").Columns.AutoFit
.Columns("A:B").Delete Shift:=xlToLeft
Application.CutCopyMode = False
End With
Application.ScreenUpdating = True
End Sub
I have a list of >50 contract numbers listed in cells A1:A350, with several of the numbers listed more than once. What I would like to do is, on a separate worksheet, list each contract number only once, in column A.
View 9 Replies View RelatedI have a Listbox1 in which I was able to populate it with unique items (from column B in Programs worksheet) using a collection (that was a good achievement for me!)
Now, I want to populate a the Listbox2 with items (not repeated) from column C, BUT only if the correspondent value on column B is equal to listbox1.value.
[Code] .....
I've been using the following formula from the xldynamic website detailing
sumproduct usage as a base for retrieving uniqe instances in a data series.
I've been trying to modify the formula to allow me to utilize one additional
criteria.
Original formula:
=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))
Here is my formula:
=SUMPRODUCT((($O$7:$O$2710<>"")*($M$7:$M$2710="1986"))/COUNTIF($O$7:$O$2710,$O$7:$O$2710&""))
I'm trying to say how many unique instances are there for column O, where
column m = "1986?"
I have a sheet of un-ordered data with duplicates. Looks a little like this:
Sheet2
*
A
B
[Code]....
What I want to do is: on a separate sheet, when the user inputs a Region name, it will return an array of unique Locations under that Region. For example, if the input is "NORTHEAST", the following would be returned:
Sheet3
*
A
B
C
[Code]....
I have a combo box (from the Control Toolbox toolbar). I like this to be populated with the contents of column A. however, in column A there can be duplicates. I obviously don't wan't any duplicates in the combobox.
View 9 Replies View RelatedI want to paste a list of customers into a sheet and then have a formula that counts the number of customers. So, even if a customer appears 10 times it would only count it once and and continues down the column and counts the next company name and so on. Then it gives me a total of the number of different companies in that column.
View 9 Replies View RelatedI have a list of sales associates by items by manufacturer. I need to count how many manufacturers are represented by each associate. It might look something like this:
Smith, John Brand A Beer 12 oz Beer Industries, Inc.
Smith, John Brand A Beer 8 oz Beer Industries, Inc.
Smith, John Brand B Vodka 700ml Vodka Industries
Jacobs, Sally Brand C Wine 1989 Wine Makers Co
Jacobs, Sally Brand C Wine 1979 Wine Makers, Inc.
Jacobs, Sally Brand D Wine 1908 Old Wine Makers, Ltd.
Jacobs, Sally Brand E Beer Six Pack Fun Beer Co.
So the totals would be:
Smith, John Total # of Manufacturers: 2
Jacobs, Sally Total # of Manufacturers: 3
So the formula ignores the duplication in employee names and manufacturer names.
Is this possible? I'm thinking something like =sumproduct((Range=AssociateName)*(sum(1/countif(ManufacturerRange, ManufacturerRange)))