count unique help

[ryan0019]

I have 3 columns of data on a data dump tab (sheet 1), 10000+ rows of data

column A - item #'s
column B - vendor #'s
Column C - batch #'s

this is all raw data from a data dump.

On sheet 2 I have all of my vendor/item combinations detailed out. I'd like to count the total unique batches that pertain to the vendor item relationship.

I've done countifs, but this is limiting (probably due to my lack of expertise). Here is my current formula.

=COUNTIFS('Quality Data Dump'!A9:A2477,'Data Aggregation'!C2,'Quality Data Dump'!I9:I2477,'Data Aggregation'!E2)

The issue I have is that I want to count all unique batches in column C that match the respective item and vendor selected, but countif requires a criteria when I create a range for column C.

Any help?

 

[ranman256]

record a macro,
build a pivot table.

 

[ryan0019]

record a macro,
build a pivot table.

No, I need a formula. thx though

 

[SpillerBD]

Even with a Pivot Table, a formula would be required. Count does not indicate the number of unique items but the number of items...
I want to accomplish in the Pivot Table since I already use Pivot Table for other sorting/detail work.

 

[SpillerBD]

See http://www.mrexcel.com/forum/excel-questions/661641-pivot-tables-count-unique.html
I specifically used link for PowerPivot solution at Unique Count in Excel Pivot Table With PowerPivot