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?

02nd Feb 2012 15:01

0

16

1932

## Replies

## Please login or register to join the discussion.

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.

Or change your thinking...

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

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

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

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.

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

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

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

SUMIFS

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

SUMIFS

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 - I'll have to look at it more carefully. Was hoping it might just be really obvious. Thanks for the tip.

Sumproduct alternative

This should work:

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

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

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

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

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

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

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