Free Microsoft Excel 2013 Quick Reference

Excel COUNT function, excluding duplicate values

hi everyone,

i hope somebody could help me on this.

i have a list of data for plant species. on the first sheet labeled "list of species", it includes the family name, common name. genera and species of plants. on the second sheet "species count", i would like to count the number of genera and species belonging to a family but excluding duplicate values.

let's say:
Sheet1

-----------A---------------B----------------------C
1---FAMILY--------GENUS-----------SPECIES
2---Rosaceae-----Rosea--------------indica
3---Rosaceae-----Rosea--------------pudica
4---Rosaceae-----Prunus-------------fragrans
5---Fabaceae-----Agathis------------columnaris
.
.
n

what i would like to do in sheet 2 is to count the unique value in column based on the range of values in column a. in sheet 2, i have already removed the duplicate values in column a leaving the unique values only. so, in sheet2, it should show computation like this:

Sheet 2

---------A--------------------B-------------------C
1---FAMILY---------GENERA------SPECIES
2---Fabaceae------------1-------------------1
3---Rosaceae------------2-------------------3
.
.
n

i have attached a sample table.

any suggestion would be greatly appreciated.


Can anyone help with a excel formula to exclude duplicate values? For example:

part # unit cost usage
aabb $10 10
aabb $10 10
ccdd $20 20
ccdd $20 20
eeff $30 15

The total value I need is $950 = (aabb unit cost * aabb usage) + (ccdd unit cost * ccdd usage) +(eeff unit cost * eeff usage). What should my formula look like?

Excel: Count Function, column c, excel 5

column c, excel 5, c1: Andrew, I kind of hate to say it, but I gave up on making up these incredibly frustrating formulas a long time ago. I would rather use VBA code, and just ...

Excel: Count Function

The Count function counts the number of cells that contain numbers as well as the number of arguments that contain numbers. ... In Excel, the Count function counts the number of ...

Excel COUNT Function - Excel 2007 COUNT Function

Excel 2007's COUNT function ... this new data . The syntax for the COUNT function is: =COUNT( Range ) Range - the group of cells the function is to count. Example Using Excel 2007's COUNT ...

Excel COUNT - Excel COUNT Function - Using Excel COUNT Function

Excel's COUNT function is a very useful function. What it does is count up the number of cells containing numbers in a selected range.

Excel COUNT Function - Excel 2007 COUNT Function

Excel 2007's COUNT function allows you to count up the number of cells containing numbers in a selected range.

Disclaimer: My experience with and knowledge of Excel is very limited.
I'm not sure if what I'd like to do can be done with a simple function
or if I can explain clearly what I'm trying to do.

I have a column (A1:A10) of totals derived from the rows that intersect
them (i.e. A1 contains =sum(B1:M1), A2 contains =sum(B2:M2), etc.) Only
A1:A6 contain non-zero values, so far.

I am calculating the MIN, MAX, and AVERAGE of the A1:A10 column. There
are two MIN issues I need to resolve:

1) Find the MIN(A1:A10) excluding zero values. I found this solution
in another posting: =MIN(IF(A1:A10=0,"",A1:A10)) and press ctrl + shift
+ enter. This works fine. This solution may not be needed if it can be
incorporated into the solution for the second part.

2) The zero values in A1:A10 will always be at the bottom of the list
until the corresponding rows are populated a row at a time descending.
So, with A1:A6 containing non-zero values, I'd like to exclude from the
MIN function not only the zero values A7:A10, but A6 also (the last
non-zero cell.) Is there a way to simply find the first zero value
cell in A1:A10 (let's say A7) and exclude A6 (A7-1) through A10 from
the MIN function on A1:A10? Does this make sense?

The reason for this is A7 remains a zero value until row 6 is fully
populated and row 7 gets its first piece of data. When A7 has a
non-zero value, this means that row 6 is now fully populated and should
be considered in the MIN(A1:10) function. At this point A7 should be
excluded even though it is no longer a non-zero value because row 7 in
not fully populated yet.

Any help would be appreciated.

Thanks,
--Robert-->

--
WeatherGuy
------------------------------------------------------------------------
WeatherGuy's Profile: http://www.excelforum.com/member.php...fo&userid=9254
View this thread: http://www.excelforum.com/showthread...hreadid=497216

Hey guys am not that well versed with excel (beginner), but could use some help from all your expertise in excel.
I am trying to compare values from 2 sheets, using a match function, however, for duplicate values, only the first reference is given. with some help from the internet, i tried using the row() to make the value unique but am getting stuck.
The formula am using is: =IF(MATCH(F6,Sheet2!$E$6:$E$107, 0) = 'Sheet2!$F$6:$F$107,F6,"") for the (value if true) i want the reference of matched value(unique), and if false return blank or "-". Where F6:F107 of Sheet 2 has the Row() to identify the row.

If there is another way to get the result i need, i would be grateful.

It would really help me a lot if this could be solved.

Thanks in advance..

Disclaimer: My experience with and knowledge of Excel is very limited. I'm not sure if what I'd like to do can be done with a simple function or if I can explain clearly what I'm trying to do.

I have a column (A1:A10) of totals derived from the rows that intersect them (i.e. A1 contains =sum(B1:M1), A2 contains =sum(B2:M2), etc.) Only A1:A6 contain non-zero values, so far.

I am calculating the MIN, MAX, and AVERAGE of the A1:A10 column. There are two MIN issues I need to resolve:

1) Find the MIN(A1:A10) excluding zero values. I found this solution in another posting: =MIN(IF(A1:A10=0,"",A1:A10)) and press ctrl + shift + enter. This works fine. This solution may not be needed if it can be incorporated into the solution for the second part.

2) The zero values in A1:A10 will always be at the bottom of the list until the corresponding rows are populated a row at a time descending. So, with A1:A6 containing non-zero values, I'd like to exclude from the MIN function not only the zero values A7:A10, but A6 also (the last non-zero cell.) Is there a way to simply find the first zero value cell in A1:A10 (let's say A7) and exclude A6 (A7-1) through A10 from the MIN function on A1:A10? Does this make sense?

The reason for this is A7 remains a zero value until row 6 is fully populated and row 7 gets its first piece of data. When A7 has a non-zero value, this means that row 6 is now fully populated and should be considered in the MIN(A1:10) function. At this point A7 should be excluded even though it is no longer a non-zero value because row 7 in not fully populated yet.

Any help would be appreciated.

Thanks,
--Robert-->

I need to count my cells that have numbers in them every cell with a number
needs to = .25 not 1. When I use the count function it uses values 1, 2, 3
I need it to count .25, .50, .75, 1

Hi,
I have a spreadsheet and I am trying to use a count function for data in specific columns only
e.g.
Column A: 1
Column B: 3
Column C: (A+B) 4
Column D: 4
Column E: 0
Column F: (D+E) 4
I am trying to get a count of the number of columns that contain the value 4 but only as an input value as opposed to the sum of any other values (if that makes sense), in this case I do not want to include columns C or F in my count function.
Anyone any suggestions?
Thanks

Hello Guys,

I have a userform with a textbox and a command button. What I want to do is when CommandButton1 is clicked, excel will look for duplicate values on Sheet1 based on what's in TextBox1. If it finds a duplicate value, a pop up message will then appear.

I hope someone can help me with this.

Thank you in advance,

JC

I have the current dataset:

Month Fund Region Name
Jan A E SH
Jan B W FW
Feb C S WR
Feb C E SH
Feb C E SH

I have written the following formula to return the unique values in
the name column:

=SUM(IF(FREQUENCY(IF('Data Sheet'!$A$3:$A
$79=(CONCATENATE(CHOOSE(MONTH(TODAY()),"Jan","Feb" ,"Mar","Apr","May","Jun","Jul","Aug","Sep","Oct"," Nov","Dec"),"-",YEAR(TODAY()))),IF('Data
Sheet'!$D$3:$D$79="FUND",IF('Data Sheet'!$P$3:$P$79="Region",IF('Data
Sheet'!$J$3:$J$79"",MATCH("~"&'Data Sheet'!$J$3:$J$79,'Data Sheet'!$J
$3:$J$79&"",0))))),ROW('Data Sheet'!$J$3:$J$79)-ROW('Data Sheet'!$J
$3)+1),1))

I want to have a formula that will return me the duplicate "Names" per
Month per Fund per Region. Basically its taking the formula above and
instead of it using frequency for unique values it would use some
function for duplicate values.

Thanks for the help.

Scott

I'm trying to pull data frm one sheet (~16000 rows) to another sheet, making use of Index and Match function from a cell (WHREIN i'm making use of function LARGE/SMALL).

The problem is, where a cell content is identical in two or more cells (where LARGE/SMALL function is used), the Index + Match Functions returns the first value that it finds in the range.

Is there any otherway round whereby, i can pull all the relevant data

Illustration

A B C D E
1 ABC DEF 36 GEF HIJ
2 PQR WZX 54 POL MNB
3 AQW WER 36 TYU GFR
4 CDF VFG 48 NJM MKL
5 CDS XSD 40 GHJ UIO
6 ZXC VBN 36 BHU OKM

As can be seen frm above, in Column C, value of 36 is being repeated on 3 occassions.

Now when i try to pull corresponding data from coulmn A/B/D or E, making use of Index+Match function, it returns value of C1 only even against C3, C6.

Thanks in advance.

I've got a few columns where I'm using =INDEX(A:A,MATCH(LARGE(D:D,1),D:D,0)) in order to find the largest number in Column D, and then display the text that's in Column A of the same row. I'm doing this to find the top 7 combinations, but my data is a couple hundred rows long. I'm running into the situation where Column D has duplicate values, and it's only displaying the Column A text from the first instance.

Data:
Combination	                                       Wins	Losses	Win %
Dodd, McMurphy, Fornelli, Hinnen, Patterson	       34	17	66.667%
Dodd, Fornelli, Hinnen, Jacobi, Patterson	       32	17	65.306%
Dodd, Fornelli, Hinnen, Patterson	               55	30	64.706%
Dodd, McMurphy, Fornelli, Hinnen, Jacobi, Patterson    18	9	66.667%
Dodd, McMurphy, Fornelli, Hinnen	               40	23	63.492%
Dodd, McMurphy, Hinnen, Patterson	               44	24	64.706%
Dodd, Fornelli, Hinnen, Jacobi	                       41	24	63.077%
Current Output:
Dodd, McMurphy, Fornelli, Hinnen, Patterson		66.667%
Dodd, McMurphy, Fornelli, Hinnen, Patterson		66.667%
Dodd, Fornelli, Hinnen, Jacobi, Patterson		65.306%
Dodd, Fornelli, Hinnen, Patterson	        	64.706%
Dodd, Fornelli, Hinnen, Patterson	        	64.706%
Dodd, McMurphy, Fornelli, Hinnen	        	63.492%
Dodd, Fornelli, Hinnen, Jacobi	                	63.077%
Is there any way to show both Column A values when the value in Column D is the same?

Preferred Output:
Dodd, McMurphy, Fornelli, Hinnen, Patterson		66.667%
Dodd, McMurphy, Fornelli, Hinnen, Jacobi, Patterson    	66.667%
Dodd, Fornelli, Hinnen, Jacobi, Patterson		65.306%
Dodd, Fornelli, Hinnen, Patterson	        	64.706%
Dodd, McMurphy, Hinnen, Patterson	               	64.706%
Dodd, McMurphy, Fornelli, Hinnen	        	63.492%
Dodd, Fornelli, Hinnen, Jacobi	                	63.077%


I have a 50,000 item list of serial numbers. What I notice is when I do a search for 45678 and then 40000 it takes a lot longer then the reverse process. This seems to slow my macro down a lot. I wanted to confirm with somebody who might know more about excels find function that this is the case.

And is there a better way? My first thought was to add starting points to search at since the serial numbers are in a general order but do skip a few spots.

Any thoughts are appreciated. Thanks.

I am working on an assignment and I have downloaded a rather large amount of data from multiple data sources. There are numerous duplicates in my dataset because each data source contains at least one datapoint that matches the datapoint of another source. A simple conditional format and search for duplicates would usually suffice. However, many of my data sources include an instance of their name in parenthesis at the end of each datapoint. The following is an example of my data:

Name Source Value **Does it find the duplicate**
Volvo AutosLTD $53,000 -------
Volvo (Cars.com) Cars.com $53,000 NO
Volvo (Auto Digest) Auto Digest $53,000 NO
Volvo Wheels $52,999 YES

In the above example, a conditional format highlighting the duplicate values would only find one of the three duplicate values.

I figured that I could simply do a Find by looking in Values and then Replace what I found with a blank. And after doing so, my table would look like as such:

Name Source Value
Volvo AutosLTD $53,000
Volvo Cars.com $53,000
Volvo Auto Digest $53,000
Volvo Wheels $52,999

But when I run a conditional format to highlight the duplicate values, Excel still does not pick up on these duplicates. I have made sure to only highlight only the one row I am interested in (Name) when I ran the conditional format. I don't know what's preventing Excel from highlighting the duplicate values. If I simply retype, say Volvo, underneath the entry I just made a change to (Find (Cars.com) & Replace with a blank), then the entry I just typed is highlighted but the one I made a change to is not.

Does anyone have any suggestions/comment? Any help would be greatly appreciated. Thanks.

Count If Function in Excel Worksheet Functions

Is there a way to use the Count if function to count if a value is between 25 and 40? Using Excel 2003 ...

Count If Function in Excel Worksheet Functions

Is there a way to use the Count if function to count if a value is between 25 and 40? Using Excel 2003 ...

Hi all,

does anyone knows how to write a formula to count the no. of duplicate vales
in a column?

For example:

Yes
No
Yes
No
Yes

When we apply the formula to a cell based on the value "Yes", the cell will
show 3.
When we apply the same formula in another cell, the cell value will show 2.

The Count formula in Excel counts only numeric values.

Any help is appreciated.

Thanks in advance.

Cheers,
HS

I have a list of values to sum, but I need to exclude some of them. My exclusion criteria is something like: if the account number is 1,2,3,4,5 or 6, exclude; otherwise, don't exclude.

The formula involves an external link, and the directory structure can be kind of deep, so when I first wrote it as a series of sumifs (sum(all) - sumif(1) - sumif(2) -sumif(3), etc.), I got a "formula too long" error the next time I tried to update the link.

I changed the formula to a sumproduct structure, and it shortened it quite a bit, but it's still a bit long, and I'm worried it may give me trouble again. I'm also just not happy with it. I'd much rather have one condition than 7 of them for my exclusion.

I haven't been able to find any function that would allow me to create a logical condition in Excel along the lines of:

value in (1,2,3,4,5,6)

The closest I came was to use vlookup and test for #N/A error with the ISNA function, but I can't get it to work for an array, and I'm trying to get one result (a sum), not an array of results.

So, to sum up the problem, I have an external workbook with two columns of interest: the account number and the amount. I want the sum of all the amounts, except for those in certain account numbers. If I write the exclusions as a series of conditions, my formula becomes too long, so I'm looking for a way to build one condition in my sumproduct formula that checks each value in an array against not one value, but a list of value, returning TRUE whenever the tested value is part of the condition list.

I think I'm looking for a way to duplicate the "element of" logical operator, as in 3 element of (1,2,3,4) returns TRUE but 6 element of (1,2,3,4) returns FALSE.

I know about the OR function, but to use it, I still have to link the external workbook in each condition within the OR function, and on top of that, I'm having trouble getting to work for an array.

Can anyone help?

Brigitte

Hallo Friends,

i just want to know the how to count the duplicate value in vba

in Vb.net there is a "Distinct" function that help the separate duplicate value and also count

In vba is there any "Distinct" function that help me to separate the duplicate and also count?

bellow i attached sample file where Column A contain Duplicate value and In column B i want to unique value of Column A

Hi,

I have about 2000 data on Excel file and many have duplicate values
(numberic) in the cell on Column A.
Is there a function that will look for duplicate and delete (or highlight)
all, but one of the duplicate value.

For example:
row 50-59 on column A has a value of 50006420, I like the macro to delete
row 51-59.
Thanks for any help.

Hello, it's my first post here, decided to register and post my problem as everyone looks very knowledgable and helpful on here and I am tearing my hair out trying to find the answer!

I've been Googling and scouring the Excel help files and can find lots of references to functions for finding and removing duplicate values in Excel, but I want to do the opposite and get Excel to report only the duplicate values and leave out the unique values it finds.

In the report I am working on, a new report is generated each month and I copy the data into a new worksheet which has exactly the same columns, formatting etc as the previous month.

Each of our customers has a unique reference number which appears in the first column, and I need to find the 'repeat offenders', i.e. customers whose reference number appears in more than one month.

I would also like the results to appear in a separate worksheet to display which customer reference numbers have been duplicated and which sheets they appear on.

I've created an example worksheet to show what I'm trying to achieve (can't post my original as it contains confidential data), if anyone knows of a way I can get Excel to do this automatically that would be great!

Thanks

Andy