|Why not just make it easy and do the math yourself with an IF statement added in the middle. So for your example, you would use this formula in the final cell where you want the total to be. |
=(A2*IF(B2>1, 1,B2))+(A3*IF(B3>1, 1, B3))+(A4*IF(B4>1, 1, B4))
By adding the IF statements in the formula, you are letting it decide if it should use the value that is there or substitute a 1 if it is over 1. Obviously, this formula will eventually grow too large if used to calculate a lot of cells, so it can either be split up by adding an extra hidden column to the sheet to calculate out each individual row and then SUM the resulting values, or it can be converted into a formula using VB.
Please let me know if you have any questions or need more assistance.
IT Desktop & Network Consultant - MOS Master Certified, MCP, MCITP - Windows 7, CCNA Certificate Pending, A+, Network +