Countif to find total sales

I have a column of names, and I want to be able to count all the instances of each name, as each instance represents a sale of a product.

Countif(Sales!B:B,"Dave") works, counting all the instances of Dave.

But if I have all the names in column A, and try to have column B give the results (from another WS), as in: =COUNTIF(Sales!B:B,'Best Customers'!A1), I get a "0" as the result. Yet XL help says countif can be used as =COUNTIF(A2:A5,A4). where A4 holds the value to search for.

While we are exploring this, is there a good way to look in a column, get every different instance of the names, and output them into another column?

TIA!

Hi,

The fact that you get a zero means that the syntax of the formula is correct. Check the values of the names in column B of the Sales sheet. The chances are that you have some trailing or leading spaces. e.g. " Dave " rather than just "Dave".

Use Data Filter Advanced Filter to copy values from one range to another range elsewhere.

HTH

That's not it, as I have MANY names in column B, and they all return a 0. If I change the name in the forumla to "Dave", it finds them all. Using a cell reference to column A yields all zeroes.

Not sure how you mean to use advanced filter to copy names... I am trying to look at a column (on the sales sheet, say 1000 names total), find all unique names (put them in a column on another sheet, say 200 unique names), then using the new column as a reference, count the number of times each name appears (how many sales each customer has).

Hi,

It's probably best if you upload your workbook, or at least a representative sample of it so that we can see your problem in context.

The Advanced Filter using the Unique option should it seems do exactly what you're looking to achieve. Have you checked out this functionality?

Rgds

Hi,

To get a unique list of customers first name the range A2:D25 on the Sales Sheet 'Data'

Copy the column label 'Customer' to A1 on the Customers tab and name A1 'Data_Out'

Now with cursor in A1 from the menu pick Data, Advanced, in the List Range type the word 'Data', select the 'Copy to another location' option, in the copy to box type 'Data_Out' and tick the 'Unique records only' box and then OK.

For your formula (in B1 on the Results sheet use a SUMIF() instead.
e.g.

Please Login or Register  to view this content.

Rgds

Thanks, Richard. I decided to create a macro to automatically do the "look for unique customers & count their purchases, then sort them", and ran into a problem. Here's my code thus far:

Please Login or Register  to view this content.

This works great up to the part where I try to sort based on the number of purchases made, and the sort gives me garbage. Why is it not sorting correctly? Both column A & B are general in format.

B U M P it up, I think it may have been missed.

Please Login or Register  to view this content.

Here are some more possibilities with Excel 2007
No formulas or code.
All dynamically updated.
Chart, colors gratis.
http://www.mediafire.com/file/zkmyymrtmzm/SalesEx.xlsx