Count By Criteria Based On Duplicates In Different Range
Feb 18, 2008
I have 2 columns on column contains 2 numbers, the other comlumn has names. We'll use A1:A4 and B1:B4 for the example.
Row A Row B
25 John Smith
50 Mike Jones
25 Chris Carter
50 Mike Jones
I want to be able to count one number in "A" but only if the name is not duplicated in "B".
My result for #25 would be 2.
My result for #50 would be 1, because Mike Jones is duplicated.
The results would be displayed in two seperate boxes, so I would only need on formula to count #25, then one formula to count #50.
View 3 Replies
ADVERTISEMENT
Jul 4, 2014
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].....
View 2 Replies
View Related
Nov 10, 2006
I have a list of several ranges defined in vba, and i am trying to find which range a cell containing word is in excel. Im am looking for help for a good way of doing this.
For an example: Say I have 2 ranges defined, the first range corresponds to column A, the second to column B (just an example). The word "Dog" is written in cell B15. I want to find out in vba what range "Dog" is in. I would like some vba code to check and find either if it in a given range (boolean) or a way of checking a list of ranges and returning which it is in. The word will only appear once on the spreadsheet. I have played around with the find command (A=range.find(Pl), then check if A exists) but i havent had any luck (I get an 'Object variable or With block variable not set) I tried searching for a while, but was having trouble finding something related to what i am trying to do.
View 7 Replies
View Related
Nov 20, 2006
Is it possible that, once filtered, you can count the amount a filled in cells in a column range...BUT! These cells are ID numbers for stocks, so CAN contain duplicates which represent accounts, Therefore, any duplicate will be counted as 1...
eg
12345325
12345325
435ghfdhy
5464OKff
SEDDONF4
[Code]...
As we can see here there are 14 lines of data but only 7 make up the dataset
so if X was the variable assigned to this it would = 7
Is this possible, in a loop or some sort, Would VBA hold all the Instances in its memory???
ERROR#9 OUT:
View 9 Replies
View Related
Nov 20, 2006
Is it possible that, once filtered, you can count the amount a filled in cells in a column range...BUT! These cells are ID numbers for stocks, so CAN contain duplicates which represent accounts, Therefore, any duplicate will be counted as 1...
eg
12345325
12345325
435ghfdhy
5464OKff
SEDDONF4
4455ONHIG
4455ONHIG
4455ONHIG
4455ONHIG
4455ONHIG
234234
66555556
66555556
66555556
As we can see here there are 14 lines of data but only 7 make up the dataset
so if X was the variable assigned to this it would = 7
Is this possible, in a loop or some sort, Would VBA hold all the Instances in its memory?
View 9 Replies
View Related
Mar 20, 2014
I have a large spreadsheet where I've successfully ranked a large series of data based on revenue, using product classification as a criteria & summarising this on another page with the top 20 products. The problem I'm having is that within certain criteria's I have some products with the exact same revenue figure, so if the 2nd ranked product within classification "C" for example has revenue of $100, & there's another product within the same classification also with $100 of revenue, on my top 20 report I have results for the 1st & 2nd ranked product, but a blank for the third product & then results again for the fourth
The formula I'm using is
=IF(Y12="NA",0,SUMPRODUCT(--(Y12=$Y$12:$Y$6000),--(AC12
View 2 Replies
View Related
Apr 3, 2013
removing duplicate names. Students were allowed to take a quiz as many times as they wanted. I need to remove the duplicate entry by keeping the highest grade.
Here is the setup of my excel file. Column 1 has surnames, Column 2, has first name, and column 3 has grade.
I can't figure out how to filter them based on first and last name because some students have the same name. with the grade as the criteria
I would need an excel formula not macro
View 9 Replies
View Related
Jun 25, 2014
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
View 3 Replies
View Related
Aug 24, 2011
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).
View 3 Replies
View Related
Mar 23, 2014
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
View 7 Replies
View Related
Jan 24, 2012
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.
View 2 Replies
View Related
Jan 20, 2014
I need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.
Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.
Book1.xlsx
View 2 Replies
View Related
Dec 3, 2008
I am trying to count AE2:AE601 if AE2:AE601 <= 2 and if AF2:AF601 = the value of AH2 but the formula that I have create returns a 0 when it should be returning a 1.
Where did I go wrong? Here is what I have so far.
=COUNT((AE2:AE601)*(IF(AE2:AE601,"<=2",IF(AF2:AF601,"="&AH2))))
View 3 Replies
View Related
Aug 25, 2006
This is probably a simple question for excel gurus, but I am a molecular biologist, and don't set up these formulas every day. I have a table full of genotypes that I have indicated as 0, 1, 2 or 3 in columns. Column heads are gene names; row heads are subjects (patient numbers). I want excel to look in row 1 in column A and see if there is a 1 and also in column B in the same row to see if there is a 1, and if both are 1, count that patient in a tally. I want to be able to get a count of the number of patients with 1's in both columns. If someone can tell me what functions I use to set up this tally, I think I can set up more elaborate formulas. At the end, I want excel to look at several columns and tally the number of patients that have 1's in several columns.
View 3 Replies
View Related
May 4, 2007
I have a spreadsheet for my customers. i Break them down in 3 categories
L c r Column o gives me the total dollar amount of all columns. I have figured out how to tell how many of each I have sepratly and there respective dollar totals
How do count how many $0 amounts in column o belong to l r c respectively
In other words if I had 10 c's how many were $0 in o. Lets say I had 10 c's but only 6 had a total greater then $0 in column o that would be 60 percent
View 5 Replies
View Related
Oct 31, 2013
I am looking to count every 4th column that has a average score less than 90. My columns are set with 3 scores and 4th column has the average of those scores. Like such:
untitled.jpg
I can count them using a SUMPRODUCT(--(MOD(COLUMN function but I cannot get a criteria to work.
Non-VBA preferred
View 4 Replies
View Related
Dec 6, 2013
Attached is a workbook that better explains what I am trying to do. I am counting pastdue accounts from several different insurance companys. I have several pastdue brackets, and I need the total amount of pastdue accounts for each bracket, for each insurance company. I think the countifs function wrapped in an index match may get the job done, but I have not been able to figure it out. right now I have a countifs wrapped in an if statement, and it is producing incorrect results.
test book 1.xlsx
View 3 Replies
View Related
Jun 29, 2009
I am attempting to count records based on 3 criteria in 3 different ranges and am hitting a wall. My formula will be on a different worksheet as it is the summary sheet.
Worksheet "Cases"
Column E - Type
Column F - Month
Column G - Year
Worksheet "Recap"
Recap month will be input into Cell I5 eg. "6" for June
Recap year will be input into Cell K5 eg. "2009"
Cells N10-N15 is a list of Types
Summary Cell J10 will need to count if
1)worksheet Cases column E = worksheet Recap N10
2)worksheet Cases column F = worksheet Recap I5
3)worksheet Cases column G = worksheet Recap K5
View 9 Replies
View Related
Aug 27, 2006
I've looked at the various examples in a search of topics simillar to my topic but I still can not get thsi to work.
I have a work book that contains 2 sheets
Sheet1 is called RawData of Multiple columns
Sheet2 is a quick report basded on Sheet1 data.
I need to find a way to do counts where 2 criteria are met.
Example.
Count the number of Times the phrase Major Victory appears in column M
( Range M1 to M50 ) of Sheet1 IF the value in Column L ( Range L1 to L50 ) of Sheet1 = USA
I tried this:
=COUNTIF(RawData!M1:M50,B1( AND(RawData!L1:L50=$A1)))
I am using B1 and A1 ( from Sheet2 ) as search criteria
but it gave me 0 ( and I know this is wrong ).
Based on the reading I did on simillar topics it appears that I cannot use CountIF in this case.
View 9 Replies
View Related
Sep 11, 2006
I have ahseet with the following columns:
Col 1 = Item Description
Col2 = Price
Col3 = Discount Rate
I would like to gather a count on another sheet of all instances in col 1 where it is equal to 'Fridge' I know I can use the SUMIF function but I would like to do it in VBA code.
View 5 Replies
View Related
Jul 24, 2014
I have a date range that I'm trying to get a total count on. The criteria is any date in COLUMN I that is not "approved" in COLUMN L and the date is less than the current date which I have in cell U5.
[Code] ......
View 12 Replies
View Related
Dec 7, 2013
What I require : Column F to be blank as long as the following is true:
1) Column A (Order) cannot be blank.
2) Column E (Status) has to be equal to either "Completed" or "Successful".
3) Column D (Sales) has to have the same date as Column C (Contact), but due to bad data quality each order number can have several rows and as long as the Sale date from one of the other "Duplicate" rows equals the contact date it needs to be counted. This is as long as there is not already a row for the Order that already has Sales / Contact day matching.
In the attached spreadsheet, I have highlighted the rows that should be blank in column F in green. My attempts at formulas to count this are in the columns highlighted in red.
WORKAROUND.xls
View 1 Replies
View Related
Mar 10, 2014
count cells row wise that meets criteria both in column & Row as well.
View 2 Replies
View Related
Apr 2, 2009
i need to count based on 2 seperate sets of criteria in the same row.
column G houses the total number of hits as a number,
Column E holds the division
Column B holds the job title,
rows 2 through 222
i need to sum up the numbers in each G field, where the G and E field match a specific text criteria.
View 7 Replies
View Related
Jan 3, 2013
I'm trying to create a formula that tells me the following:
If the item is marked as closed (vs. active), then how many renewals/terminations replacements/etc are there? These are two difference columns in a worksheet.
View 2 Replies
View Related
Dec 2, 2008
using various keywords, and I haven't been able to find a solution that works. It would seem SUMPRODUCT is what I need to use, but thru all the combinations I've tried, I still can't get the syntax right. The problem is: I have dates in col A, and the word "Holiday" in col D. I need to create a formula that counts the number of instances of the word "Holiday" in col D, only where the date in col A is =DATE(2008,1,1))*(A11:A45
View 9 Replies
View Related
Feb 19, 2009
I'm facing a big problem and I can't found a solution for days.
I'm trying to count/sum unique values in a Range, base on a criteria excluding Blank cells. Basicly I'm using the Formula Below:
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))
Let me Put to you an example for you been able to understand my problem.
Example considering Range "C2:C20" exluding blank cells
(1) - To count unique values
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))
(2) - To count unique values based on criteria in range "B2:B20"
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E"))
(3) - To sum unique values on "E2:E20" based on same criteria
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")*(E2:E20))
They all work fine. But I need to insert a merge at Row 10 for
better reading purposes and the (2) and (3) are returning (#DIV/0). The (1) formula still work's.
I just don't know what to do guys ....
View 9 Replies
View Related
Jul 21, 2009
I need to have multiple ranges count based on several criteria. The hitch is that needs to count if one or more criteria is met not all. I tried using a COUNTIFS however that seems to evaluate based on AND not OR.
Is there a nested COUNTIFS or similar function that would work?
View 9 Replies
View Related
Feb 17, 2008
My data is like:
A1: 5 -- B1: X
A2: 5 -- B2: X
A3: 5 -- B3: Y
A4: 3 -- B4: Y
A5: 3 -- B5: X
A6: 8 -- B6: Y
A7: 2 -- B7: X
A8: 3 -- B8: Y
A9: 1 -- B9: X
I want the count of unique numbers in column A based on the criteria i have on column B.
So the count for X should be 4 and for Y should be 3..
Can i achieve this using formula only?
View 5 Replies
View Related
May 17, 2012
I have two ranges of data and want to count the unique number of times the values in column A occur if they have two values in column B. For Example:
ColA ColB
333310143333306344441014444430635555161246666101466661612466663063
I want to count the number of times value in ColA has the value 1014 AND 3063. My intention is to create a table that cross references the two
10143063161241014331306333116124112
I searched and found answers where multi criteria were in multi columns but I can't seem to find a solution to this means of find the unique count in ColA.
View 3 Replies
View Related
