# 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
Microsoft ExcelMicrosoft Office

Last Comment
Excel amusant
Saqib Husain

I made it to work using a UDF

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 i
End Function
Function critval(x)
critval = Val(x)
If Right(Trim(x), 1) = "%" Then critval = critval / 100
End Function
``````
Excel amusant

Please check and let me know if anything is missing.

Max Possible 12% does this mean IF the sum is above 12% you wanted to show 12% only else the sum of your range?
Example-3z_2.xlsx
BajanPaul

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.
Excel amusant

This how I did. Did you see the formula and results.

in the formula I took into account column H and J into account which the max is only 4%.

Plz Let me know if you want any changes.
Excel amusant

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
BajanPaul

Meets my requirements.  Thanks for the help!
Excel amusant

You welcome!
Microsoft Excel

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

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY