Share this content
0
16
2019

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

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?

Replies

Please login or register to join the discussion.

avatar
By BKD
02nd Feb 2012 15:26

Why not ...

... 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.

Thanks (1)

Or change your thinking...

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

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

Thanks (1)
avatar
02nd Feb 2012 15:31

How about this?

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

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

 

 

Thanks (1)
avatar
By BKD
02nd Feb 2012 15:43

Beat me to it, Steve

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 (1)
By chatman
02nd Feb 2012 15:51

Thanks.

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.

Thanks (0)
avatar
06th Feb 2012 14:09

SUMIF...How about SUMIFS

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.

Thanks (1)
By chatman
06th Feb 2012 14:18

SUMIFS/COUNTIFS

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

Thanks (0)
By chatman
06th Feb 2012 20:34

SUMIFS

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

Thanks (0)
avatar
10th Feb 2012 13:26

SUMIFS

 

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

Thanks (1)
By chatman
10th Feb 2012 13:49

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

Thanks (0)
avatar
10th Feb 2012 15:36

Sumproduct alternative

This should work:

 

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

Thanks (1)
By chatman
10th Feb 2012 15:39

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

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

Thanks (0)
avatar
10th Feb 2012 16:06

Whoops

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

Thanks (1)
By chatman
10th Feb 2012 16:11

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

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

Thanks (0)
avatar
10th Feb 2012 16:44

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

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 (0)
By chatman
10th Feb 2012 17:10

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.

Thanks (0)