I want to auto-sum and not exceed a certain number
 

I have a column that I am using auto-sum. The total of that column cannot
exceed a certain number. What is the formula?

Answer: I want to auto-sum and not exceed a certain number
 

To auto-sum a column and ensure that the total does not exceed a certain number, you can use the SUMIF function in Microsoft Excel. Here are the steps to follow:

1. Determine the maximum total that you want to allow for the column. Let's say that the maximum total is 100.
2. Select the cell where you want to display the total.
3. Type the following formula into the cell:
   Code:

   ---

   =SUMIF(range,""&0,range)-SUMIF(range,""&100,range)

   ---

4. Replace "range" with the actual range of cells that you want to sum. For example, if you want to sum cells A1 to A10, the formula would be:
   Code:

   ---

   =SUMIF(A1:A10,""&0,A1:A10)-SUMIF(A1:A10,""&100,A1:A10)

   ---

5. Press Enter to calculate the total.

The formula works by first summing all the values in the range that are greater than 0 (""&0). This ensures that only positive values are included in the total. Then, it subtracts the sum of all the values in the range that are greater than the maximum total (""&100). This ensures that any values that would cause the total to exceed the maximum are excluded from the total.

I want to auto-sum and not exceed a certain number
 

Hi,

Change 1000 to whatever your maximum value

=MIN(1000,SUM(A:A))

Mike

"jt4lsu" wrote:

I have a column that I am using auto-sum. The total of that column cannot
exceed a certain number. What is the formula?

I want to auto-sum and not exceed a certain number
 

=MIN(100,SUM(A1:A10)) will return 100 if the SUM is more than 100


Gord Dibben MS Excel MVP

On Mon, 12 Jan 2009 00:43:01 -0800, jt4lsu
wrote:

I have a column that I am using auto-sum. The total of that column cannot
exceed a certain number. What is the formula?

I want to auto-sum and not exceed a certain number, if exceed, show a pop up message warning. Thanks in advance for response