Eliminating Lowest Value From the Sum of Cells

[jdwiser]

Hi all! I have a situation where I need to remove the lowest value from the sum of cells that are not in successive order in a row or column. Can you help me with the formula to obtain this?

Example:

A1 - 7
C1 - 9
E1 - 12
G1 - 5
I1 - 10

I need to be able to obtain the total for all of the numbers yet drop the lowest value which is (G1 - 5) in this example. What is a nice formula that gives me the sum of 38?

Thank you!
David

[mike7952]

Try this

Please Login or Register  to view this content.

[mehmetcik]

Please Login or Register  to view this content.

[CK76]

Is there numeric values in between the range?

If not, you can try..
=SUMIF(A1:I1,"<>"&MIN(A1:I1)).

[JohnTopley]

try

=SUM(A1:I1)-MIN(A1:I1))

SUM and MIN ignore text used in this form

[jdwiser]

All worked great for me. I greatly appreciate all of your help with this formula. Have a great day!!

[FlameRetired]

Another way that skips the "in between" cells. Array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[FlameRetired]

Another way non-array.

Please Login or Register  to view this content.

[jdwiser]

Follow-up question to all of you since you were so much help. For presentation purposes, I would like to be able to highlight the lowest value that is being left out of the total. I've gone to Conditional Formatting and having difficulty figuring out how to apply it to my situation. Any ideas? Thanks again!

[CK76]

Highlight/select A1:I1

Bring up CF menu and select use formula.

Enter following formula and change format as you wish.
=A$1=MIN($A$1:$I$1)

[jdwiser]

That works great. Is there something I can add to the formula that will highlight just one value when there are duplicate low numbers in the sum? Like in my above example, let's say there are two 5's. I'm finding it highlights both 5's yet the formula only kicks one out which is correct. Thanks again!

[shg]

< deleted >

[CK76]

Is there something I can add to the formula that will highlight just one value when there are duplicate low numbers in the sum?

Does it matter which minimum value is highlighted?

[FlameRetired]

@ jdwiser

What is the specific range to apply in Format Manager? Is it $A$1,$C$1,$E$1,$G$1,$I$1 or $A$1:$I$1?