=sum(min(35,f2):min(35,n2)) doesn't work and it won't accept curly brackets.

Posted by chatman PM | on Thu, 02/02/2012 - 15:01 1409 16 comments | report

I am trying to sum cells F2 to N2 but if any cell value exceeds 35,I want to limit it to 35. =sum(min(35,f2):min(35,n2)) doesn't work and it won't accept curly brackets. I could get round this by adding another sheet that does the min(35,f2) etc bit but would prefer not to. Can anyone help?

Comments

There are 16 comments. Login or register to view them.

Why not ...

BKD | Thu, 02/02/2012 - 15:26

Or change your thinking...

Steve Kesby | Thu, 02/02/2012 - 15:30

How about this?

Burbage Accounting | Thu, 02/02/2012 - 15:31

Beat me to it, Steve

BKD | Thu, 02/02/2012 - 15:43

Thanks.

chatman | Thu, 02/02/2012 - 15:51

SUMIF...How about SUMIFS

richwhight | Mon, 06/02/2012 - 14:09

SUMIFS/COUNTIFS

chatman | Mon, 06/02/2012 - 14:18

SUMIFS

chatman | Mon, 06/02/2012 - 20:34

SUMIFS

richwhight | Fri, 10/02/2012 - 13:26

@richwhight

chatman | Fri, 10/02/2012 - 13:49

Sumproduct alternative

RichardSchollar | Fri, 10/02/2012 - 15:36

=SUMPRODUCT(MIN(35,F2:N2))

chatman | Fri, 10/02/2012 - 15:39

Whoops

RichardSchollar | Fri, 10/02/2012 - 16:06

=SUM(IF(F2:N2>35,35,F2:N2))

chatman | Fri, 10/02/2012 - 16:11

=SUM(IF(F2:N2>35,35,F2:N2))

David Taylor | Fri, 10/02/2012 - 16:44

Thanks

chatman | Fri, 10/02/2012 - 17:10