aggregating two lists

[onsman]

I have two lists. First is alphanumeric data (eg. "AB10", BD12"...). Second is floating point numeric.

I need to create a crosstab style table which the first list down the left, and counts in the following columns of the number of records within specific ranges.

There may be two or more records with the same alphanumeric data reading down eg.
AB10 | 1234.56
AB10 | 456456456
AB12 | 45.7
AB45 | 3455

I am familiar with SUMPRODUCT crosstab style tables on two numeric lists, but this does not seem to work as first column is alphanumeric.

Any thoughts on how to adapt one?

Cheers

 

[fairwinds]

Welcome to the bord!

Maybe something like this:

E4: =SUMPRODUCT(--($A$2:$A$5=$D4),--($B$2:$B$5>=E$1),--($B$2:$B$5<=E$2))

Drag right / down.

Book1
	A	B	C	D	E	F	G
1	Part	Value		Min	1	2000
2	AB10	1234.56		Max	1000	10000
3	AB10	456456456		Part
4	AB12	45.7		AB10	0	0
5	AB45	3455		AB12	1	0
6				AB45	0	1
Sheet5