We help IT Professionals succeed at work.

count in excel

darlene_shepherd
on
I want to know how many unique entries are in a column - based on two other columns

by customer by date how unique items are in column 3.

Let me know if I am not clear.
Thanks,
Comment
Watch Question

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
you are not clear :) post a sample excel file...
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
One way is to use a formula like this

=SUM(IF(FREQUENCY(IF($A$2:$A$10=$E2,IF($B$2:$B$10=F$1,IF($C$2:$C$10<>"",MATCH($C$2:$C$10,$C$2:$C$10,0)))),ROW($C$2:$C$10)-ROW($C$2)+1),1))

where col A has dates, column B Customers and column 3 is the data to count.....and E2 is a specific date and F1 a specific customer

That's an "array formula which has to be confirmed with CTRL+SHIFT+ENTER

see attached example

regards, barry
27426294.xls
CERTIFIED EXPERT

Commented:
Two options Countif()

and sumproduct()

Check them out and let us know if you need more help.
CERTIFIED EXPERT

Commented:
Oh unique entries?  There is an advanced filter that can filter a list in place and only show unique values, that may be easiest.
CERTIFIED EXPERT

Commented:
Ummm Data-> Filter section -> Advanced Button -> screenshot
Advanced-Filter-Unique-Items.jpg

Author

Commented:
As requested here is a portion of the file
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
Two possible solutions:  
1) SUMIFS function
Insert the following in cell D2 and copy down.
=COUNTIFS($B$2:$B$8650,B2,$A$2:$A$8650,A2)

I do not recommend the above solution.  It is processor intensive (time-consuming) and produces redundant results.

2) Pivot Table:
Set up Customer as Row, Date as column OR sub-row, Tags as values.

I've attached a modified workbook demonstrating both.


 forexperts-mod.xlsx
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
To see the actual tag numbers and count, add "Tags" as another sub-row category below date in the pivot table.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
put this into D2, and copy down

=countifs(A:A, A2, B:B, B2)
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
oops above one does not give unique tag count, it gives count of tags for that day for that customer...

Author

Commented:
I need a unique count - pivot table or formula isnt giving me that
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
My formula suggestion above will give a unique count......but it's too inefficient for such a large amount of data.....

Here's an alternative - download Morefunc add-in from here

and then use COUNTFDIFF function, specifically this formula in D2

=COUNTDIFF(IF(A$2:A$10000=A2,IF(B$2:B$10000=B2,C$2:C$10000)),FALSE,FALSE)

confirmed with CTRL+SHIFT+ENTER

It worked for me with your data but it might take a minute or two - I'm not sure Morefunc is comapatible with Excel 2010....it is with excel 2007

regards, barry
CERTIFIED EXPERT
Commented:
Pivot table by customer and date.  Advanced filter by unique items...  done.
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
Okay, my original solutions count the number of occurances, not unique tags.

A simple way to correct this is to use the Remove Duplicates function - on the first three columns only.  This drops the total number of rows from over 8000 to 1283, preserving the unique tags.  The COUNTIFS and Pivot Tables will now correctly display the unique counts.

Author

Commented:
I ended up creating a pivot table with the unique tags per date per customer - then I used that to create another pivot table to count the number of unique tags per date per customer - then I did a vlookup to assign the # of tags to the individual rows in my source data - now am able to use it for my original pivot table analysis that has other data in it.
Thanks for your help - I will review some of the formulas above for future learning.
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.