Very hard to slove

[smartguy]

Hello All,

Good day !!!!

I have excel in the below format.

Excel Workbook
	A	B	C	D
1	Name	Desc	Status	Spend
2	ravi	Good boy	1	10
3	kumar	Ave	3	10
4	ravi	Good boy	1	20
5	ravi	Good boy	2	30
6	kumar	AVE	1	40
7	suresh	MED	1	50
8	sub	MED	3	60
9	ravi	Good boy	2	70
10	ravi	Good boy	3	80
11	ravi	Good boy	1	20
Sheet1

I need sum of value based on name and status.


And also i need Descrption also.



It is possible in VBA.

Excel Workbook
	F	G	H	I	J
1	Name	DESC	Status 1	Status 2	Status 3
2	ravi	Good boy	50	100	80
3	kumar	AVE	40		10
4	sub	MED			60
5	suresh	MED	50
Sheet1

Please help on that...

 

[Blade Hunter]

Why not use a pivot table?

 

[smartguy]

I am not get DEsCprtion....

 

[smartguy]

ANY reply on that

 

[smartguy]

Any Help

 

[Sandeep Warrier]

How about

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Name	Desc	Status	Spend		Name	DESC	Status 1	Status 2	Status 3
2	ravi	Good boy	1	10		ravi	Good boy	50	100	80
3	kumar	Ave	3	10		kumar	AVE	40	0	10
4	ravi	Good boy	1	20		sub	MED	0	0	60
5	ravi	Good boy	2	30		suresh	MED	50	0	0
6	kumar	AVE	1	40
7	suresh	MED	1	50
8	sub	MED	3	60
9	ravi	Good boy	2	70
10	ravi	Good boy	3	80
11	ravi	Good boy	1	20
Sheet1

Excel 2003

Cell Formulas
Range	Formula
H2	=SUMPRODUCT(--($A$2:$A$11=$F2),--($B$2:$B$11=$G2),--($C$2:$C$11=RIGHT(H$1,1)+0),$D$2:$D$11)

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[nightcrawler23]

Function sumSpend(myRange As Range, sName As String, desc As String, status As Integer) As Double
Dim temp As Integer
temp = 0
Set myRange = Range(Cells(2, 1), Cells(11, 4))
t = myRange.Rows.Count
For i = 1 To t
    If (myRange(i, 1) = sName And myRange(i, 2) = desc And myRange(i, 3) = status) Then
        temp = temp + myRange(i, 4)
    End If
Next i
sumSpend = temp
End Function

paste this code in any module and the below formula in the cell H2

=sumSpend(A2:D11,F2,G2,1)

change 1 to 2 for status 2

 

[smartguy]

But How can i get the descrption field

 

[Sandeep Warrier]

If there is only one description per person then

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Name	Desc	Status	Spend		Name	DESC	Status 1	Status 2	Status 3
2	ravi	Good boy	1	10		ravi	Good boy	50	100	80
3	kumar	Ave	3	10		kumar	Ave	40	0	10
4	ravi	Good boy	1	20		sub	MED	0	0	60
5	ravi	Good boy	2	30		suresh	MED	50	0	0
6	kumar	AVE	1	40
7	suresh	MED	1	50
8	sub	MED	3	60
9	ravi	Good boy	2	70
10	ravi	Good boy	3	80
11	ravi	Good boy	1	20
Sheet1

Excel 2003

Cell Formulas
Range	Formula
G2	=INDEX($B$2:$B$11,MATCH(F2,$A$2:$A$11,0))
H2	=SUMPRODUCT(--($A$2:$A$11=$F2),--($B$2:$B$11=$G2),--($C$2:$C$11=RIGHT(H$1,1)+0),$D$2:$D$11)
I2	=SUMPRODUCT(--($A$2:$A$11=$F2),--($B$2:$B$11=$G2),--($C$2:$C$11=RIGHT(I$1,1)+0),$D$2:$D$11)
J2	=SUMPRODUCT(--($A$2:$A$11=$F2),--($B$2:$B$11=$G2),--($C$2:$C$11=RIGHT(J$1,1)+0),$D$2:$D$11)

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Peter_SSs]

A PivotTable was suggested and I think you can get what you want with that. Here it is:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Desc	Status	Spend		Sum of Spend		Status
2	ravi	Good boy	1	10		Name	Desc	1	2	3	Grand Total
3	kumar	Ave	3	10		kumar	Ave	40		10	50
4	ravi	Good boy	1	20		ravi	Good boy	50	100	80	230
5	ravi	Good boy	2	30		sub	MED			60	60
6	kumar	AVE	1	40		suresh	MED	50			50
7	suresh	MED	1	50		Grand Total		140	100	150	390
8	sub	MED	3	60
9	ravi	Good boy	2	70
10	ravi	Good boy	3	80
11	ravi	Good boy	1	20
12
PivotTable