Counting Unique Values in a table based upon criteria of other columns

Hi,

I looked at your problem and used a method the works in the attached spreadsheet.

See what you think.

Cheers

TonyB

Thanks for the replies guys. I'm still having a bit of trouble even with your suggestions, so I figured I'd make a more vivid and detailed example for you to look at if you still will.

Attached is a file that has 3 sheets:

#1 as the "Report Sheet" which is where I need the number of unique customers shown (area colored orange).

#2 is the "Master Sales Table" which holds those 250,000 records. I made the table look much more like my actual sales table, but still need the same criteria from it. This table is linked directly from Access, so in referencing a column I reference the entire column (i.e. $A:$A).

#3 is the "Customer List" table which is simply a list of all of my customers, their name, and then their State that they are located in if that helps.


I'm hoping to be able to write a formula that can give me the number of unique customers who have positive sales in 2011 who reside in VT. Once I get that, obviously altering the formula for the year 2010 or a different state will be easy.

Thanks so much for the help guys!

Hi,

I have added another Sheet Called Data Split.

The purpose is to maintain the Formula's there.

As you modify you Master Sales Table, everything on the Data Split Sheet will update.

As will your Report Sheet.

Check all the formula's in the whole workbook.

Formula's in Column's A - L can be fiiled down the page, as can Column's N thru T.

However, the SUM values in Row O3 to T3 will ne to be expanded to capture all values.

See what you think.

Cheers

TonyB

I see where you're going with your example, however doing that for about 300,000 rows is something excel/my computer does not like to process using the drag tool. 300,000 rows by 50 states x 5 different years is a lot for it to handle..

Hi,

Just a thought, how do you fill down?
The quickest method is to select the cell you wish to fill down, then while holding the CTRL and Shift Keys use you Arrow Down Key. Then once selected use CTRL D to fill.

Cheers

TonyB