Touff_One_Not able to find suitable formula

[chandrajitshahkumar]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Test Type[/TD]
[TD="align: center"]Marks[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Priyanka[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Unit Test[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]91[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Prakash[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Unit Test[/TD]
[TD="align: center"]85[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Rani[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Unit Test[/TD]
[TD="align: center"]75[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]bablu[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Unit Test[/TD]
[TD="align: center"]65[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Rajesh[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Unit Test[/TD]
[TD="align: center"]86[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Manish[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Unit Test[/TD]
[TD="align: center"]69[/TD]
[/TR]
[TR]
[TD]Priyanka[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Mid Test[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]82[/TD]
[/TR]
[TR]
[TD]Prakash[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Mid Test[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]75[/TD]
[/TR]
[TR]
[TD]Rani[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Mid Test[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]64[/TD]
[/TR]
[TR]
[TD]bablu[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Mid Test[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]59[/TD]
[/TR]
[TR]
[TD]Rajesh[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Mid Test[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]66[/TD]
[/TR]
[TR]
[TD]Manish[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Mid Test[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]92[/TD]
[/TR]
[TR]
[TD]Priyanka[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Final Test[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]82[/TD]
[/TR]
[TR]
[TD]Prakash[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Final Test[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]83[/TD]
[/TR]
[TR]
[TD]Rani[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Final Test[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]81[/TD]
[/TR]
[TR]
[TD]bablu[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Final Test[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]78[/TD]
[/TR]
[TR]
[TD]Rajesh[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Final Test[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]79[/TD]
[/TR]
[TR]
[TD]Manish[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Final Test[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]90[/TD]
[/TR]
</tbody>[/TABLE]

Use one formula which will fill up the box according to the above table in which test type, marks and student name is mentioned.
Marks should be appropriate according to the student name and test type

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]Unit Test[/TD]
[TD="align: center"]Mid Test[/TD]
[TD="align: center"]Final Test[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Priyanka[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Prakash[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Rani[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bablu[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Rajesh[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Manish[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AliGW]

Assuming that your test data starts in A2 and your results table is in E1:H7, put this into the first cell of your results table (F2) and copy down and across:

=SUMIFS($C$2:$C$19,$A$2:$A$19,$E2,$B$2:$B$19,F$1)

 

[L. Howard]

Try this and pull down. With you data in columns A, B, C

I numbered the Marks 1 to 18 for easy test viewing.

Howard

Excel 2012
	G	H	I	J
1		Unit Test	Mid Test	Final Test
2	Priyanka	1	7	13
Sheet4

Cell Formulas
Range	Formula
H2	=SUMPRODUCT(($A$2:$A$19=$G2)*($B$2:$B$19=H$1)*($C$2:$C$19))
I2	=SUMPRODUCT(($A$2:$A$19=$G2)*($B$2:$B$19=I$1)*($C$2:$C$19))
J2	=SUMPRODUCT(($A$2:$A$19=$G2)*($B$2:$B$19=J$1)*($C$2:$C$19))

 

[chandrajitshahkumar]

Hi L. Howard and AliGW if you allow me then please share your email id so that i can get my proper solution.

 

[AliGW]

No, that is not the way it works. Please continue here, explaining what is wrong with the solutions offered. Is it that your dataset is different to the sample?

 

[chandrajitshahkumar]

After hundred times trying finally i got it.. Solution will contain IF function and Vlookup as well.. Actually the solution you have offered is not correct as i am not able to explain it to you so please try it in your excel sheet then you will able to find the errors which i got

 

[AliGW]

I created the solution in my spreadsheet with your data, and it works perfectly. I am sorry that you were not able to make it work properly, but you must have made an error yourself.

 

[mikerickson]

The solutions that they offered give the desired results indicated.
Can you post an example of data and desired results for which the proffered formulas don't work and explain the logic behind the desired results.

 

[chandrajitshahkumar]

Hi AliGW will you please post your solution with the given data..

 

[chandrajitshahkumar]

No need to post your solution with the data.. Its working thanks a lot AliGW