# SUMPRODUCT with criteria to evaluate items greater than 1 as 1

Hi,

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

EG.
ColA -------------- ColB
DAY RATE------ FTE
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)

Nope found the answer on another forum

=SUMPRODUCT(IF((B1:B3)>1,1,(B1:B3))*(A1:A3))

this is an array formula that needs to be conformed by Ctrl+Shift+Enter

### A B C 1

A B C
1 150 0.9 =IF(B1>1,1,B1)
2 200 0.8 =IF(B2>1,1,B2)
3 220 1.2 =IF(B3>1,1,B3)
4
5 =SUMPRODUCT(A1:A3,C1:C3)