SUMIF formula runs partially

I am using a SUMIF formula nested inside an IF statement. Sometimes my formula computes what I need to happen, other times it shows as an error. The order of the Logic is as follows:

If(Cell Value="No Change",Cost/Total Cost of No Change,
If(Cell Value="Partial Sale",Cost/Total Cost of Partial Sale,
If(Cell Value="Partial Purchase",Cost/Total Cost of Partial Purchase,
If(Cell Value="New Purchase",Cost/Total Cost of New Purchase,
False))))

An example of what I wrote:

=IF(CK8="No Change",BY8/SUMIF($CK$8:$CK$4996,"No Change",$P$8:$P$4996),
 IF(CK8="Partial Sale",BY8/sumif($CK$8:$CK$4996,"Partial Sale",$P$8:$P$4996),
 IF(CK8="Partial Purchase",BY8/SUMIF($CK$8:$CK$4996,"Partial Purchase",$P$8:$P$4996),
 IF(CK8="New Purchase",BY8/SUMIF($CK$8:$CK$4996,"New Purchase",$P$8:$P$4996),FALSE))))

My issue comes when Cell Value="Partial Sale" or "Partial Purchase", I keep getting an error. I check my Cost column and every cell has a value (range from negatives to posties including zero). I have also converted this into a SUMIFS formula and no change occurred. I also used the * before and after my text values because I saw somewhere it was required, but no change. What could be the issue?