Excel Sumproduct based on IF criteria

Microsoft Excel 2003 (full product)
February 9, 2012 at 04:13:05
Specs: Windows 7

I have an issue in evaluating actual project spend for people working on the project.
The cost is evaluated by DAY RATE x FULL TIME EQUIVALENT (FTE).
The FTE figure is used for evaluation of cost and also for the evaluation of the additional effort used over normal hours worked.

My issue is that I need to record where resources work over 1 FTE (ie. over their allotted 7 hours a day) however when evaluating the cost if greater than 1 is entered I need to evaluate it as 1

ColA -------------- ColB
150 ---------------- 0.9
200 ---------------- 0.8
220 ---------------- 1.2

So I need SUMPRODUCT to evaluate as (150*.9)+(200*.8)+(220*1) {rather than 220*1.2}

I feel I need to use sum product as I also have a mechanism in place where a user can add a further resource line (hence I can simply use (150*.9)+(200*.8)+(220*1)

Please help

See More: Excel Sumproduct based on IF criteria

Report •

February 14, 2012 at 16:53:24
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 +


Report •
Related Solutions

Ask Question