The trick is to get the Dollar Signs correct. use Sumproduct

=SUMPRODUCT(--(H$1=$A$2:$A$10),--($G2=$B$2:$B$10),$C$2:$C$10)

I put the summary table in the range G1:J4. column C2:G4 had the codes and
H1:J1 had the RoomNo. I put the formula in cell H2 and then copyied it to
range H2:J4. The only problem I see is with the Number 01, 02, 03. tTey
have to be in the same format in column A as they are in H1:J1.

"jbjtc" wrote:

Can anyone please help with the following:

I have a table as follows:

Col A Col B Col C
Room No. Code Qty
01 Unit 1
01 W/top 2
01 Sundry 3
02 Unit 5
02 W/top 1
02 Sundry 1
03 Unit 7
03 W/top 4
03 Sundry 3

I need to transpose the table so that Codes are in column A, followed by
each room no. as a column header, with qty's against each code, as follows:


Col A Col B Col C Col D
01 02 03 <--- Room No.'s
Unit 1 5 7
W/top 2 1 4
Sundry 3 1 3

I've tried loookup's and index/match functions, but to no avail.

This is very urgent, so if anyone can help, i would be very grateful.

Kind regards

--
jj