Excel/Excel Sum Help

Advertisement


Question
QUESTION: I have a column of dollar amounts in column I.  The situation is as such.  The first $250 is to be paid by a certain party then the rest of the total is split up by two parties.  

I need a cell to reflect this first 250. My best guess would be as such...in cell P5, if less than or equal to 250 then sum of column I, else 250.

Let me know if I need to be clearer than this.

ANSWER: Scott

How about this

=SUMIF(I1:I100,"<=250",I1:I100)+COUNTIF(I1:I100,">250")*250


The sumif formula sums all amounts less than or equal to 250.

The countif formula counts the number of times that the amounts in the range exceed 250 then multiplies that number by 250 and the total of that is added to the sumif total.

All amounts that are less than 250 are included at their actual value and all amounts greater than 250 are included at 250 only.

I think this is what you wanted.

Richard
Florida
USA

---------- FOLLOW-UP ----------

QUESTION: That didn't work...it appears that it just gave a total of column I.  Let me put it a different way in case I was not clear.

The first $250 of the total amount of column I is to be paid by a certain party then the rest of the total is split up by two parties.

I need a cell to reflect this first $250. My best guess would be as such...in cell P5, if sum of column I is less than or equal to 250 then P5 equals the sum of column I, if sum of column I is greater than 250, then P5 equals 250.

Answer
Scott

This is what my original formula will do
if the number is 500 it will add 250
If the number is 125 it will add 125
If the number is 642 it will add 250
If the number is 25 it will add 25
etc, etc.

As you see it adds all of the numbers that are 250 or less substituting 250 for any number that is more than 250.

If that is not right then I have misunderstood and I still do not understand from you current explanation.  If it is correct then try my first solution again. I'm thinkin that you may have written the formula incorrectly, so I suggest that you copy and paste the formula directly into your excel sheet  (it has been tested and does work as explained above), you may however have to change the row numbers to fit your situation, but if the column is column I then nothing else should need to be changed.

Richard  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Richard Roberts

Expertise

Can assist you in most areas of Excel, have been working with it for about 15 years in many types of applications, but primarily in financial and accounting applications. I am a CPA and many client or client problems have necessitated the use of excel. I am not an expert in charting, macros, or pivot tables.

Experience

Have been working with Excel for about 20 years primarily in accounting and financial areas.

Education/Credentials
BA, Certified Public Accountant

©2016 About.com. All rights reserved.