Long excel 2010 sumif multiple conditions exists help

Good evening Gurus,
I have an excel workbook that I need a function to calculate a final % based upon certain conditions. I am attaching it for review. Please help if possible.. I am hoping I can achieve with one long function. Please review the PValues to check to see if they need to be included in the final sum. Thanks for the help! Example-3z_2.xlsx

Enter this formula in N13
=totperc(B13:G13,$B$11:$G$11,$B$10:$G$10)+totperc(L13:M13,$L$11:$M$11,$L$10:$M$10)+IF(OR(totperc(H13,$H$11,$H$10),totperc(I13,$I$11,$H$10)),$H$10)+IF(OR(totperc(J13,$J$11,J$10),totperc(K13,$K$11,$J$10)),$J$10)

You would also have to change the cells H10 and J10 to have a single numeric value each.

Function totperc(r1 As Range, r2 As Range, r3 As Range) Dim i As Integer For i = 1 To r2.Cells.Count Select Case True Case r2.Cells(i) = "Y" If r1.Cells(i) = "Y" Then totperc = totperc + r3.Cells(i) Case Left(r2.Cells(i), 1) = ">" If r1.Cells(i) > critval(Right(r2.Cells(i), Len(r2.Cells(i)) - 1)) Then totperc = totperc + r3.Cells(i) Case Left(r2.Cells(i), 1) = "<" If r1.Cells(i) < critval(Right(r2.Cells(i), Len(r2.Cells(i)) - 1)) Then totperc = totperc + r3.Cells(i) Case Left(r2.Cells(i), 2) = "=>" If r1.Cells(i) >= critval(Right(r2.Cells(i), Len(r2.Cells(i)) - 2)) Then totperc = totperc + r3.Cells(i) Case Left(r2.Cells(i), 2) = "=<" If r1.Cells(i) <= critval(Right(r2.Cells(i), Len(r2.Cells(i)) - 2)) Then totperc = totperc + r3.Cells(i) Case Right(r2.Cells(i), 2) = "Up" If r1.Cells(i) >= critval(Trim(Left(r2.Cells(i), Len(r2.Cells(i)) - 2))) Then totperc = totperc + r3.Cells(i) Case Right(r2.Cells(i), 2) = "Dn" If r1.Cells(i) <= critval(Left(r2.Cells(i), Len(r2.Cells(i)) - 2)) Then totperc = totperc + r3.Cells(i) End Select Next iEnd FunctionFunction critval(x) critval = Val(x) If Right(Trim(x), 1) = "%" Then critval = critval / 100End Function

Yes.. The max possible sum is 12%.
In cells H10 - I10 = EITHER OR / J10 - K10 = EITHER OR so the max can only be 4% for H10-J10. All others sum range if they fall within the tolerance.

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Enter this formula in N13

=totperc(B13:G13,$B$11:$G$

You would also have to change the cells H10 and J10 to have a single numeric value each.

Open in new window