• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COUNTING NON-DUPLICATES

bibanl

New Member
Hi,

i have about 6 + columns, i.e data, category, amount and name.

I need to know how many unique names are in a column provided other conditions have been met. i.e.


How many unique names are there, in January,under water catgory, who paid more than $0?

=countifs(a1:a7000,"=january",b1:b7000,"=water",c1,c7000,">0") =46


I want to add another formula that will count how many unique names are in column d, provided the above conditions have been meet. How can i do that easlity, as i have about 30 categories to do the same thing to? Can excel do that?


Please help and thanks for your time and effort
 

Hui

Excel Ninja
Staff member
Assuming you have used Named Ranges to define your data

I would use something like

=+SUMPRODUCT(+1*(Date=DATE(2009,2,8))*(Cat="b")*(Amount>20)*(Name="Steve"))

or

=+SUMPRODUCT(+1*(Date=DATE(2009,2,8))*(Cat="b")*(Amount>20))

where

Date is the Date Range for your data

Cat is the category range

Amount is the amount range

Name is the Name range

I would also use Named areas for your query data

ie: qDateFrom from Date

qDateTo to Date

qCat for Category

qAmount for Amount

qName for Name

and then use

=+SUMPRODUCT(+1*(Date=qDateFrom)*(Cat=qCat)*(Amount>qAmount)*(Name=qName))


You can select between a date range by

=+SUMPRODUCT(+1*(Date>=qDateFrom)*(Date<=qDateTo)*(Cat=qCat)*(Amount>qAmount)*(Name=qName))


There are lots of good web sites to assist with this style of query
 

bibanl

New Member
Hi Hui,

thanks for the response but it doesn't help me. I am trying to avoid setting a condition for each name because i have about 3000 names.

Basically what i need is a formular that will give me the number of customers who


1. Select the number of customers in January

2. Of those in January, how many bought water

3. Of those in January and bought water, how many paid more than $0.00

4. And finally, spit out a number that does not contain duplicates because i have a customer who could have bought 6 times i.e. they appear 5 more times, yet i just want them to be counted once.


So far i have managed to pick the number of customers in january, those who bought water and paid more than 0.00 and the answer i have is 46, but this number includes duplicates. How do i exclude the duplicates in my formula.


Any help with this.
 

keymaster

New Member
okay.. if we take this step by step, as you say you already have the list of 46 folks, you can easily get the count of uniques with sumproduct.


=SUMPRODUCT(1/COUNTIF(A1:A46,A1:A46))


more here: http://chandoo.org/wp/2009/08/06/count-number-of-unique-cells/
 

Hui

Excel Ninja
Staff member
You could also try using a pivot table

Select all your data

Construct a Pivot Table

Put the Amount field in the Rows Category and Put the Dates and other fields in the Column Categories

You may need to group the various Column Fields to meet your needs

Then simply double click on the Grand Total of the Row that match your criteria, to show all the records that meet that criteria
 
Top