CODEWITHSUNDEEP
excel
osk
osk

Reputation: 63

how to sum an amount above a specified number in Excel

i am working with a table where all the values above 145 are marked red. i want to add only the amount greater than 145 in these values. e.g if i have 150 in one cell and 150 in other cell the result would be 10. i tried using sumif but it add all the numbers above 145. i can use if formula to calculate the values above 145 and then add them but i will have to make another table for that. if there a formula to cover this in one step so i dont have to make a separate table? i have to do this for every column.

table

Upvotes: 1

Views: 361

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

You can use SUMIF() with COUNTIF() and subtraction.

=SUMIF(A1:A100,">145")-145*COUNTIF(A1:A100,">145")

enter image description here

Upvotes: 1

Related Questions