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

=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  987  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
BKD's picture

Why not ...    1 thanks

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

... stick in another row and hide it?

No doubt, there is a formula or function somewhere that will do the job, but an extra row seems pretty simple.

Steve Kesby's picture

Or change your thinking...    1 thanks

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

... I think you'll find that this works:

"=COUNTIF(F2:N2,">35")*35+SUMIF(F2:N2,"<=35")"

How about this?    1 thanks

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

Not very pretty but it works if you are using 2010 or 2007!

=SUMIF(F2:N2,"<35",F2:N2)+COUNTIF(F2:N2,">=35")*35

 

 

BKD's picture

Beat me to it, Steve    1 thanks

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

I was going to suggest the following very similar:

SUMIF(F2:N2,"<35")+COUNTIF(F2:N2,">=35")*35

 

EDIT: and beaten to it by B A!

Thanks.

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

SUMIF!!!!!!!! Why didn't I think of that? So embarrassed. Mind you, I like the imaginative use of COUNTIF too.

Thank you very much everyone for both the quality and the speed of your responses.

SUMIF...How about SUMIFS    1 thanks

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

If you use Excel 2007 / 2010 then you can use SUMIFS which has 127 criteria should you need to get complicated. A load more like COUNTIFS became available in Excel 2007 and are worth looking at.

SUMIFS/COUNTIFS

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

Thanks richwhight. Never heard of  SUMIFS. Will have a look.

SUMIFS

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

So how would I use SUMIFS to do what I am trying to do in this case?

SUMIFS    1 thanks

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

 

chatman wrote:

So how would I use SUMIFS to do what I am trying to do in this case?

 

It was just in-case you needed more complicated criteria later. I think you were using array formulae so it might be of general interest.

 

Rich

@richwhight

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

@richwhight - I'll have to look at it more carefully. Was hoping it might just be really obvious. Thanks for the tip.

Sumproduct alternative    1 thanks

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

This should work:

 

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

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

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

Oohh, I like that one. Never heard of SUMPRODUCT before. Must look into it.

Whoops    1 thanks

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

Sorry, danger of not checking stuff before submitting.  The Sumproduct didn't work, but the follwoing array formula would (although I prefer the first mentioned formulas using Countif and Sumif):

 

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

 

which must be confirmed with Ctrl+Shift+Enter

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

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

That is what I was trying to do originally (unsuccessfully). Thanks.

David Taylor's picture

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

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

Chatman

The above formual does work - I have checked it.

As Richard said though it is an Array formula and you MUST hit Ctrl + Shift + Enter when entering the formula.

Thanks

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

Thanks. I used to use array formulae quite a lot seven years ago, but am obviously out of practice.

I never suggested that the formula provided didn't work.