# Sumif greater or equal to

1. ## Sumif greater or equal to

I am looking to do a sumif (I think it should be a Sumif).

See the attached example. Basically I want to sum in a formula a list of numbers, however, if the numbers are greater than the keys max number I want it to take the max number.

So in my example the max value of A is 3, so a value of 4 should be treated as a 3. In my example column J is how I want the numbers to be treated. I'd like to avoid duplicating the table and just have it do these calculations in the formula.  Register To Reply

2. ## Re: Sumif greater or equal to

Try

=MIN(F7,VLOOKUP(E7,\$A\$4:\$B\$5,2,0))  Register To Reply

3. ## Re: Sumif greater or equal to

That would work for a line by line basis, but I need something to put in F16 that sums F7:F12 like that.  Register To Reply

4. ## Re: Sumif greater or equal to

Why?

I would think it is beneficial to have the 'adjusted' amount shown for each individual line. Then sum that.
If you just put a formula that results in 9 instead of 15, That would be odd to someone looking at the sheet and sees numbers that add up to 15.

It is not a 'bad thing' to use extra cells to complete a calculation. Excel gives you over 1 million rows and 16 thousand columns.
That's over 17 billion cells. You might as well use a few of them.

In fact, splitting calculations into multiple cells usually makes that calculation more efficient.
Cramming all that calculation into 1 formula will be less efficient.  Register To Reply

5. ## Re: Sumif greater or equal to

I have quite a bit of data in the actual spreadsheet, multiple columns and rows, not just one like the example, additionally, the spreadsheet is subject to growth and I would rather have a single formula to avoid any cell referencing concerns that might happen. If a single formula isn't plausible then I can make the other work, just the way this spreadsheet is setup a single formula would be ideal.  Register To Reply

6. ## Re: Sumif greater or equal to

I tried, but am unable to work out a single formula method.  Register To Reply

7. ## Re: Sumif greater or equal to

ARRAY formula. Pl see note. ``Please Login or Register  to view this content.``  Register To Reply

8. ## Re: Sumif greater or equal to

Excellent, thank you!  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1