How do i do this without pivot table?

[xyz123]

Hi all

I need help with a particular excel problem.

I have a table which is like follows:

A1 C S1 10
A2 C S2 10
A3 C S1 5
A4 I S1 10


I need to convert this into something like this. The format of the table gets lost when i post but what i want to do but basically i want to count sum of Cs and Sum of Is against S1, S2.

C I
S1 15 5
S2 10 0

I cant use pivot table as i have to do further complex calculations which i cant do on pivot table results.

any ideas please?

 

[xyz123]

Couldnt edit the initial post but forgot to add that the data will be in one worksheet but results in another sheet (both still in same workbook).

 

[grizle]

You could create a manual pivot table using SUMIF but that only allows a single criterium to select the items to add. However it is possible by adding a new column to the original data contatenating the C/I and S1/S2 conditions.

On the other hand why not use a pivot table and then extract each item using GETPIVOTDATA into a copy of the pivot table. Any calculations should then be possible.

 

[Hermanito]

Why not use the pivottable anyway, and use a (manually created) copy of the pivottable data for any further calculations?

 

[Peter_SSs]

To build the table manually, choose depending on your version of Excel. In each case the formula shown is copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H
1	A1	C	S1	10		2007+	C	I
2	A2	C	S2	10		S1	15	10
3	A3	C	S1	5		S2	10	0
4	A4	I	S1	10
5						2003-	C	I
6						S1	15	10
7						S2	10	0
Table

 

[xyz123]

Thanks Peter. it worked! Just out of interest in 2003 formula what does "- -" do?

 

[Peter_SSs]

Thanks Peter. it worked! Just out of interest in 2003 formula what does "- -" do?

Have a look at these couple of links:
http://www.mrexcel.com/forum/showthread.php?t=259450
http://www.mrexcel.com/forum/showpost.php?p=337954&postcount=8