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

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

Why not ...

BKD |
BKD's picture

Or change your thinking...

Steve Kesby |
Steve Kesby's picture

How about this?

Burbage Accounting |

Beat me to it, Steve

BKD |
BKD's picture

Thanks.

chatman |

SUMIF...How about SUMIFS

richwhight |

SUMIFS/COUNTIFS

chatman |

SUMIFS

chatman |

SUMIFS

richwhight |

@richwhight

chatman |

Sumproduct alternative

RichardSchollar |

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

chatman |

Whoops

RichardSchollar |

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

chatman |

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

David Taylor |
David Taylor's picture

Thanks

chatman |