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.

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.

- Exclude duplicate values from out of a calculating a sumproduct
- Excel: Count Function, column c, excel 5
- Excel: Count Function
- Excel COUNT Function - Excel 2007 COUNT Function
- Excel COUNT - Excel COUNT Function - Using Excel COUNT Function
- Excel COUNT Function - Excel 2007 COUNT Function
- Min Function Excluding Zero Values & More
- Using match function for duplicate values
- Min Function Excluding Zero Values & More
- How do I Change the value in a count function from 1 to .25
- Excel count function for specific cells
- Command Button to Look for Duplicate Value on a Sheet
- Finding Duplicate Values
- Alternate to Index+Match functions with duplicate values
- Large function with duplicate values
- How does excels find function actually "find" values?
- Excel Can't Find Duplicates???
- Count If Function in Excel Worksheet Functions
- Count If Function in Excel Worksheet Functions
- Count duplicate values in worksheet
- exclude several values with one condition
- How to count duplicate value in vba
- Function to find duplicate values, then delete
- Function to return values duplicated in more than one worksheet?

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?

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 ...

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 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'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 2007's COUNT function allows you to count up the number of cells containing numbers in a selected range.

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

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..

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-->

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

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

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

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

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.

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%

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.

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.

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

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

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

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

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

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.

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

No luck finding an answer? You could always try Google.