help with excel formula (conditional summing of values)

I have two colums and want to add up the values in column A, except the one number that corresponds to the maximum in colum B.

So for example

1 3
5 7
6 9
4 1
3 3

I would like excel to add up 1 + 5 + 4 + 3 (and not add 6 as 9 is the maximum in colum B). I have 163 rows so nesting is not possible.

Any solutions? I've been wrecking my head...

Can you sort the two columns using values in column B as sort code, then the largest value in column B will be in the last row. The corresponding value in column A can then be excluded from your total for column A.

Is this what you're looking for?

=SUMIF(B1:B5,"<>" &TEXT(MAX(B1:B5),0),A1:A5)

That's exactly it! Thanks a million, it works perfectly, although would you mind explaining the "<>"&TEXT bit - so I know for future reference. I had tried the formula in all sorts of variations, but not this one!

And thanks Billo, it's just I have a multitude of corresponding columns in the one sheet with the maximum always being in a different place, so the sorting wasn't an option.

Yeah, no problem. Basically, the criteria parameter in SUMIF has to a be a string. So, given the specific example you give, you'd put in

=SUMIF(B1:B5,"<>9",A1:A5)

As in, find everything that's not equal to 9. Off course the problem with that is the value 9 will change with a different column of figures.

So, to get around that you need to replace 9 with the Maximum value in the column, hence MAX(B1:B5). That gives you a number back. Since the criteria has to be a string, you need to convert that number to a string using the TEXT function. Then you just add "<>" and the string together using & to create one big string. At that stage, you have a string in the criteria field.

Hope that makes sense!

Makes total sense, this is where I went wrong, I did not realise that the criteria had to be a string and all my attemps putting in the max function just came back with zero.
thanks a million, you've saved me a lot of work!

Hiya,

I have just encountered another problem and it's taken me ages to work out why the formula wouldn't work... I just don't know how to fix it... my columns are quite long (164 rows) and for some strange reason the formula doesn't correctly identify the max (which in one particular case is 151.0731245), but if I remove the decimals, it recognises 151.00 and then it works and I have no idea why? As the 151.0731245 is also derived by formula I can't really get rid of the decimals.
Any ideas?

I found a solution, this works
=SUMIFS(G4:G162, N4:N162, "<"& MAX(N4:N162))
Many thanks again for sending me down the right path though!

No problem.... Most likely thing is that TEXT was dropping the decimal places in the column, but looks like you don't actually need the TEXT function anyways. Shure you've figured it all out at this stage anyways.