Please dont kill your brain by applying nested ifs...

Your's can be much more simplified by using the vlookup/lookup or sumproduct in one line formula....

Just attach a workbook by covering the 5 examples with expected output so that we will suggest a simple method for you...