0
2129

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

• ### What goes on Tax Return for Captial Gain

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

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)
02nd Feb 2012 15:30

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

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

Thanks (1)
02nd Feb 2012 15:31

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)
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)
06th Feb 2012 14:09

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