count occurence and present results

[Guest]

I have a large file with 3 columns of information: item, serial number,
number of cust service calls for that serial number. Serial numbers are
unique and number of calls is 1 thru 5+.

How do I count the number of serial numbers for each item, that gets 1
calls, 2 calls, 3 calls, etc.

I want to present my results in a table
1 2 3 4 5
item a
item b
item c

 

[VBA Noob]

Hi,

Think this works. This assumes

1). Your sheet called Data - Change as required
2). A2 = Item A. Make sure $ in front of A2
3). B2 = Serial Number again make sure $ in front of B2
4). C1 = No of calls one. Change $C$1 to $D$1 etc for 2 calls etc.
Again use absolute values

=SUMPRODUCT(--(Data!$A$2:$A$100=$A2)*(--(Data!$B$2:$B$100=$B2)*(--(Data!$C$2:$C$100=$C$1))))

VBA Noob

 

[Guest]

Hobbes --

Two ways. By far the easiest is to create a PivotTable. It has a wizard,
and allows you to look at the same data lots of different ways. If you're
really set on creating your own table:

Let's say your data is in columns A, B, & C with 1000 rows:
D E F G H I
1 # OF SERVICE CALLS
2 1 2 3 4 5
3 m A
4 o B
5 d C
6 e D
7 l E

Cell E3 is:

=SUMPRODUCT(--($A$1:$A$100=$D3),--($B$3:$B$32=E$2))

Then just copy the formula to complete the table.

HTH