Vlookup using if condition or any other way to slove problem
 

Dear friends & members
I have a data on 1 single sheet in the below form (just an example) in
which 1 cell shows month, second shows name and third shows again
result my problem is that i want the total result in another form
Month Name Score
jan Bob 100
jan Tim 80
jan Jim 90
feb Jim 87
feb Tim 67
feb Bob 56
mar Jim 87
mar Tim 94
mar Bob 46
apr Bob 97
apr Tim 59
apr Jim 75



which is stated below
Name Jan feb mar apr total
Bob 100 56 46 97 299
Tim 80 67 94 59 300
Jim 90 87 87 75 339

The problem is that name are not in the symetric form (name are up and
down in every month). i want to use vlookup for picking the right
value but the thing is that I want that it should pick up right value
for right month
Any other way just tell me also....

hope you understand by the example thank you

thanks for advance
dhir

Vlookup using if condition or any other way to slove problem
 

Assuming data in Sheet1 and new table in sheet2, with names in column A and
months in row1:

=SUMPRODUCT(--(Sheet1!$A$2:$A$13=B$1),--(Sheet1!$B$2:$B$13=$A2),(Sheet1!$C$2:$C$13))

Copy across and down

"dhir" wrote:

Dear friends & members
I have a data on 1 single sheet in the below form (just an example) in
which 1 cell shows month, second shows name and third shows again
result my problem is that i want the total result in another form
Month Name Score
jan Bob 100
jan Tim 80
jan Jim 90
feb Jim 87
feb Tim 67
feb Bob 56
mar Jim 87
mar Tim 94
mar Bob 46
apr Bob 97
apr Tim 59
apr Jim 75



which is stated below
Name Jan feb mar apr total
Bob 100 56 46 97 299
Tim 80 67 94 59 300
Jim 90 87 87 75 339

The problem is that name are not in the symetric form (name are up and
down in every month). i want to use vlookup for picking the right
value but the thing is that I want that it should pick up right value
for right month
Any other way just tell me also....

hope you understand by the example thank you

thanks for advance
dhir

Vlookup using if condition or any other way to slove problem
 

dhir

Create a pivot table from the data. Put Name in the row field, Month
in the column field, and Sum of Score in the data area. You will have
at table that looks just as you want it.

Good luck.

Ken
Norfolk, Va


On Jun 15, 3:47 pm, Toppers wrote:
Assuming data in Sheet1 and new table in sheet2, with names in column A and
months in row1:

=SUMPRODUCT(--(Sheet1!$A$2:$A$13=B$1),--(Sheet1!$B$2:$B$13=$A2),(Sheet1!$C$*2:$C$13))

Copy across and down



"dhir" wrote:
Dear friends & members
I have a data on 1 single sheet in the below form (just an example) in
which 1 cell shows month, second shows name and third shows again
result my problem is that i want the total result in another form
Month Name Score
jan Bob 100
jan Tim 80
jan Jim 90
feb Jim 87
feb Tim 67
feb Bob 56
mar Jim 87
mar Tim 94
mar Bob 46
apr Bob 97
apr Tim 59
apr Jim 75

which is stated below
Name Jan feb mar apr total
Bob 100 56 46 97 299
Tim 80 67 94 59 300
Jim 90 87 87 75 339

The problem is that name are not in the symetric form (name are up and
down in every month). i want to use vlookup for picking the right
value but the thing is that I want that it should pick up right value
for right month
Any other way just tell me also....

hope you understand by the example thank you

thanks for advance
dhir- Hide quoted text -

- Show quoted text -

Vlookup using if condition or any other way to slove problem
 

Hi,

I like the pivot table solution but if you want to use sumproduct you can
also write it:

=SUMPRODUCT(N($A$11:$A$22=F$10),N($B$11:$B$22=$E11 ),$C$11:$C$22)
=SUMPRODUCT(($A$11:$A$22=F$10)*($B$11:$B$22=$E11)* $C$11:$C$22)
=SUM(($A$11:$A$22=F$10)*($B$11:$B$22=$E11)*$C$11:$ C$22) array entered
--
Cheers,
Shane Devenshire


" wrote:

dhir

Create a pivot table from the data. Put Name in the row field, Month
in the column field, and Sum of Score in the data area. You will have
at table that looks just as you want it.

Good luck.

Ken
Norfolk, Va


On Jun 15, 3:47 pm, Toppers wrote:
Assuming data in Sheet1 and new table in sheet2, with names in column A and
months in row1:

=SUMPRODUCT(--(Sheet1!$A$2:$A$13=B$1),--(Sheet1!$B$2:$B$13=$A2),(Sheet1!$C$-2:$C$13))

Copy across and down



"dhir" wrote:
Dear friends & members
I have a data on 1 single sheet in the below form (just an example) in
which 1 cell shows month, second shows name and third shows again
result my problem is that i want the total result in another form
Month Name Score
jan Bob 100
jan Tim 80
jan Jim 90
feb Jim 87
feb Tim 67
feb Bob 56
mar Jim 87
mar Tim 94
mar Bob 46
apr Bob 97
apr Tim 59
apr Jim 75

which is stated below
Name Jan feb mar apr total
Bob 100 56 46 97 299
Tim 80 67 94 59 300
Jim 90 87 87 75 339

The problem is that name are not in the symetric form (name are up and
down in every month). i want to use vlookup for picking the right
value but the thing is that I want that it should pick up right value
for right month
Any other way just tell me also....

hope you understand by the example thank you

thanks for advance
dhir- Hide quoted text -

- Show quoted text -