trying to count unique occurences w/in data that matches multiple criteria

hi all,

I may be completely off track (if so, please put me right) but I think that sumproduct should/will provide a solution as I try to count unique occurences w/in data after checking that it matches multiple criteria (converted to single cells as strings)...

I've had a look at Chip's duplicate/unique items & his array formulae page but can't seem to adapt them for my purpose.

Background:
In an extracted (from AS400 to Excel) Stock on Hand Report of about 4000 lines of cheese, the stock is "uniquely" identifed by Location (double alpha), sLot (short Lot, provides factory (single alpha) & date (ddmmy) of manufacture), and Material (six digit & single alpha).
However, due to an unforeseen programming issue, since resolved, a number of lines were created with duplicate Lots (single alpha for end market, sLot, & 3 letter code for end use) mapped against a multiple Materials (should be unique to a single material).
The programmer's may develop a report in the future, but in the mean time...

What is a formula I can use to get a count of the multiple materials based on stock that has the same Location & sLot(that can be expanded out to cover 4000 ish rows)?

The examples below should give answers of 2 & 3 respectively:
Loc&sLot LocMat&sLot
IPC16080 IP104256EC16080 (#1)
IPC16080 IP106101EC16080 (#2)
IPC16080 IP106101EC16080 (#2)
IPC17080 IP106101EC17080

Loc&sLot LocMat&sLot
IPC16080 IP104256EC16080 (#1)
IPC16080 IP101601EC16080 (#2)
IPC16080 IP106101EC16080 (#3)
IPC17080 IP106101EC17080

My attempts so far:
Count of Different Mat's based on same Loc&sLot
2 =SUMPRODUCT(($R$2:$R$5=R2)*1,($V$2:$V$5<>V2)*1)
#VALUE! =SUMPRODUCT(SUMPRODUCT(($R$2:$R$5=R2)*1),($V$2:$V$5<>V2)*1)

Thanks in advance,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

Hi Special K,

Thanks for the reply but unfortunately that's a no go :-(
Try it on the test data & it gives a result of 1 for all rows.

I'll try to clarify further, my aim is to:
1) minimise the size of the array (within the formula rather than by filtering or pivoting on a sheet) to be just the ones with a matching (the current row) Loc&sLot, and,
2) then count the unique LocMat&sLot strings on the rows that are included in the "filtered" array.

Or, & this is more the logic I'd try for a macro:
If Loc&sLot = current row's Loc&sLot (for all), then count unique LocMat&sLot on corresponding rows... maybe some sort of countif?

Sorry about the formulae - just copied it straight from the sheet where I'm trying various options. Changing this to be Loc&sLot and LocMat&sLot (already concatenated columns) in col A & B respectively:
=SUMPRODUCT(($a$2:$a$5=a2)*1,($b$2:$b$5<>b2)*1)
The above is my attempt @ part 1) & attempt @ part 2).


Can any one else help, maybe Bob Phillips, with your strength on sumproduct formulae?

Thanks in advance,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...