I need to count a column of text excluding duplicates, but only if they are duplicates of the cell directly above them in the column. Example:
a
b
c
c
d
d
a
b
I want this to return 6 instead of 4. Is this possible?
I have a list of invoices numbers in column B, some are duplicated
I want to count the the number of invoices excluding duplicates.
I have the below table of data and what I am looking for is a formula that will count the number for unique numbers (col E) depending on a several criteria. So far I have got this formula but it's not working!
=IFERROR((SUMPRODUCT(--(A3="2014"),--(B3="1"),--(D3="Boc Limited"),)),"")
A
B
C
D
E
year
week
smsupplier_code
supplier name
orders_no
[code].....
I want to count the number of cells in column C with one forumla that answers the question, "How many of the cells in column C contain the words, APM, BPPM, CAPM, and NMS while exluding any results that have "OUTLOOK" in Column K?
View 2 Replies View RelatedI have a column with something like 20 thousand rows. I would like a formula that basically checks the column for duplicates and numbers the sets of duplicates. So if there are 5 cells that are all "Server10" I would like for the formula to place a one next to all of those cells. And for the next set of duplicates it finds I would for it to place a 2 next to all of those cells. Essentially my end goal is to sort the sheet so that all of the matching duplicates are all together. There might be some simple filtering method here that I'm overlooking but if there is..... I'm overlooking it lol. And to be clear I don't want to eliminate the duplicates, I just want them grouped together.
I've attached a very simple example of what I would like the formula to do. The cells in the right column would be the result of the formula.
I have data in two columns where column A is a name and column B might be a synonym or might just equal the value in column A. I want to count duplicates if cell A2 matches any other cell in column A or B, unless the only match is with cell B2. If cell A2=B2, but also matches cell A7 then it should be counted as a duplicate. I've searched through a lot of the threads and found a lot of useful information, but nothing that seems to do what I need.
View 4 Replies View RelatedBelow is the criteria I need a formula for.
First Criteria - If column D=John Smith,
Second Criteria - Excluding duplicates in column A, (red are the unique values I need)
Third Criteria - Sum of column B that meets the above criteria first
Result I am after is
John Smith - $154,330.00
James Brown - $150,520.00
David Williams - $161,390.00
A
B
C
D
[code]....
I am trying to come up with a formula that will count everything excluding 1 in one row, while looking at another row to determine the group.
The attached example explains things a lot better.
I am going to have 2 formulas. 1 for the "Big" group and one for the "Small" The formula needs to look first at the column that has the group in it. Then it needs to count everything is column A excluding "Snake" And return the value.
How I can create a simple formula to count unique values/text within a range of cells that contain duplicates, blanks and errors?
For e.g., in Column A (row 1 - 10):
Proj-001
Proj-001
Proj-002
Proj-004
#N/A
#N/A
Proj-007
Proj-002
In short, I would like a pivot table to only count unique values, but when I click into the pivot I would like to show all instances of that value. For example:
I have a table of data that I am creating a pivot table from. There are fields for Customer ID, Task Name, Age, and Notes. There will be multiple records for a single Customer ID each time it has new notes.
I would like to create a pivot table that has Task Name in the Row Labels, Age in the Column Labels, and count of Customer ID in the Values, so that, for example, I can see how many accounts have been in the Design task for 2 days. However, when I do this it counts each record, but I would like it to count each unique Customer ID. Also, when I click into the pivot, instead of pulling up one line per Customer ID, I would like it to pull up each instance of Customer IDs in that Task Name/Age combination (similar to doing a DISTINCT in SQL).
I have a list of isometric drawing numbers ending with a [underscore]weld number e.g. 1692-SG-0040-04_05.
Some welds are repaired--in that scenario the amended weld number will be 1692-SG-0040-04_05R1, and even 1692-SG-0040-04_05R2 if repaired for a second time.
On occasion a weld may be cut out entirely and a new weld done. The weld number for that will be 6317-FG-1690-02_06C1.
And here's a wrinkle I've just verified...a cut weld may also be repaired so the weld number will look like 1698-SG-0077-01_04C1R1.
Is there a formula to count these as one weld:
1692-SG-0040-04_05
1692-SG-0040-04_05R1
1692-SG-0040-04_05R2
This as one weld:
6317-FG-1690-02_06
6317-FG-1690-02_06C1
6317-FG-1690-02_06C2
...and this as one weld:
1698-SG-0077-01_04
1698-SG-0077-01_04C1
1698-SG-0077-01_04C1R1
I am having trouble creating a function to count duplicates of duplicates.
An example of the data table 1 is:
Product 1 2nd
Product 1 2nd
Product 1 New
Product 1 New
Product 1 Flt
Product 2 2nd
Product 2 New
Product 2 New
Product 2 Flt
Product 2 Flt
Product 3 2nd
Product 3 2nd
Product 3 2nd
Product 3 New
Product 3 Flt
I created a new table (table 2) and made a list of all the Products on table 1 and removed the duplicates. I now have 3 columns with titles New, 2nd and Flt as follows:
New 2nd Flt
Product 1 XX XX XX
Product 2 XX XX XX
Product 3 XX XX XX
I am trying to count the duplicates for each product (XX), but I can't seem to work it out. I've tried the MS help function, but unsure of the actual formula I need to be using.
I am using Windows 7 and MS Office 2010.
I have a COUNTA function that I'm using. However, I want to exclude "N/A" from my count, what is the function that I need to use to do that.
Currently I have =COUNTA($C$10:$C$122). How do I modify this formula so it doesn't include "N/A" in my count.
I have pivot table that is pulling data from a page that is using the vlookup formula. I would like the table to only include fields that have data in the count. However, the pivot table is registering cells that have "0" (i.e., there's no actual data in the cell it is pull from) as having data. How can I get the pivot table count to ignore these cells?
View 1 Replies View Relatedhow to count days excluding only sundays in a calender period.
"weekdays" excludes both saturdays and sundays.
Have several rows
Some have data some do not
Some rows are hidden (Hidden rows also may or may not have data)
I wish to only count how many rows are not hidden.
I have a holiday and sickness chart for all of the guys in my maintenance department for the whole of next year (2014). The holidays part works fine. The sickness is split into two areas; the number of occurrences of sickness and the total number of days sick. To make the formula work for the number of occurrences it is necessary to enter sick in both Sat and Sun if the sickness spans the weekend. But we do not work the weekend and so I do not want to count those two days. The formula needs to cover the entire year (cells BL5:ZL5). The calculation that I am currently using is =COUNTIF(BL5:ZL5,"Sick")/2 (divided by two as we log a.m. and p.m. and the answer is in days). In the example attached, it would be 1 occurrence of sickness and 3 days sick, not 4 as shown.
For completeness, the formula that I am using for the occurrences is =SUMPRODUCT((BL5:ZL5="Sick")*(BL5:ZL5<>BK5:ZK5))
I have a list of courses that are run each month, I have a formula that counts the occurrences of these words to show how many times each course is run per month.
However, there are 'other' courses that will be run adhoc which I want to count the occurrences of. It basically needs to count if there is text there but exclude the normal courses which are run.
I have attached a spread sheet as an example, the list of the normal courses are on the right. I've highlighted in yellow where I need the formula.
I was wondering if was possible to create a conditional calculation excluding hidden columns.
For example in C4:HA4 the cells may have a number of values L, S, U etc.
When a user access the spreadsheet certain columns a hidden based on comparing their PC user ID to an access list in the workbook.
What I would like to be able to do would be to count the instances of L in C4:HA4 but exluding the hidden columns.
I'd hoped I might be able to use SUBTOTAL in a similar way to Domenic came up with here:
=SUMPRODUCT(SUBTOTAL(9,OFFSET(C2:C100,ROW(C2:C100)-ROW(C2),0,1)),--(C2:C100
I am trying to set up a sick leave list which will work out the cumulative total for the last 12 months (from last day of last sick entry). The problem I have, as the is is added to, I need a function/formula to work out 12 months from last date and then add up the no. of sick days from then to the last date. Hope this is clear. The next problem is I need it to work out total days and then without weekends. Below is the chart I have to work out.
Column 1 Start date of sickness
column 2 End date
Column 3 the number of days sick in period (column 2 less column 1 in days)
column 4 the number of days sick in period above excluding week ends (column
2 less column 1, less weekends in days)
column 5 total sick days in last 12 months from date in column 2 going back
a year including weekends
column 6 total sick days in last 12 months from date in column 2 going back
a year excluding weekends
What function or formulas can I use to calculate column 3,4,5 & 6. I am a not very experienecd in Excel
Formula to count the number of consecutive zero starting from the last cell with non zero value.
View 14 Replies View RelatedIs there a function that will count a column of numbers where that will eliminate counting empty cells based on a certain date that will also capture any numbers that is added after refreshing the table from Access? I have attached a spreadsheet for an example. I need to count on the number in column V that equals 13 for the date of 12/22/09.
View 5 Replies View RelatedI am trying to count the distinct times a value shows up in a column, if another column has a 2 in it.
For example:
Columns
A, B
2,P25
2,P25
3,P5
3,P6
2,P5
The results shoud be: (2) Because I only want to count the P25 one time.
I'm using Excel 2010
Here I come with yet another question in excel, how do I count/sum text in a column. E.g
Column B
Fish
Eggs
Meat
Fish
Fish
Chicken
this would basically be # Fish 3, # Eggs 1, # Meat 1, and # Chicken 1. Hope I make it clear,
On the attached spreadh sheet I have production costs that I wish to calculate. There are certain courses that are a constant each month and they are input via a data validation list, however there will be courses that are run that are not on this list.
on the cell with 'Other' I want to total the costs for the courses that exclude the normal monthly courses.
Ive managed to use the formula below to exclude certain text but I cannot do it for multiple keywords, or maybe there is a simpler formula.
=SUMPRODUCT(--(G4:G31>0),L4:L31,--ISNUMBER(FIND("Suicide",G4:G31)))
I am trying to sum data in a column based on the absence of a character in a related column.
That is to say I need to be able to exclude rows that contain an "m" (there may be multiple characters in the criteria cell)
how to count how many times a word or text is displayed in a column.
Say this is Column A Can any formula be used to tell me that T1409 was duplicated 3 times and T1527 was used 2 times. The rest of the numbers only triggered once so I dont care for those numbers. I only want T1409 and T1527 to pop up and tell me how many times it was used.
StoreT1409T1409T1527T1527T1360T2463T1547T0164T1976T2482T1409T2760T1507T0224T2458T0201T2398
I have a worksheet where
Column D lists a service offered by a business, such as IMC, TC, Respite, etc
Column M lists how long the customer has used this service for in weeks, such as 4,5,3.5,6 etc
What I would like to do in another column is to count the number of clients who have used the service for 6 weeks or more broken down by the service type i.e. Count how many people have used IMC for 6 weeks or more.
If possible I would also like to be able to count those who have used a service for between 5.1. and 5.9 weeks too, again by service i.e. Count how many people have used IMC for between 5.1. and 5.9 weeks.
Is there a way of Copying the Characters in a Cell but excluding the text from the copy.
EG:
305 GP
1,328 GP
10,250 GP
1,337 GP
But i only want the numbers and not the GP.
GP is Always at the end.