Using=LEFT as a criteria for SUMIF

Using=LEFT as a criteria for SUMIF

Didn't find your answer?

I have a list of account codes and balances, but with my sumif formula I want the criteria to be based on the first 2 letters of the account code, by using =LEFT for example. I have been trying this but getting now where fast - can anyone help?

=sumif($A$2:$A$2000,left($A$2:$A$2000,2)="MT"),$C$2:$C$2000)

Thanks
J

Replies (14)

Please login or register to join the discussion.

avatar
By listerramjet
19th Feb 2008 08:53

Hi Geoff, I get a similar
buzz from solving a problem, but like jc I much prefer the simple solution, not least when it has to be supported later.

Thanks (0)
avatar
By User deleted
18th Feb 2008 18:34

Nested IF or ELSEIF ......
Are you using nested IF statements or plain old ELSEIF; looks very much like the latter.

Anyone can make a simple thing complex but the real art is simplifying a complex issue so that everyone can understand it

Thanks (0)
avatar
By geoffemtacs
18th Feb 2008 16:51

Hi Alistair
You're right of course. In such gobbledygook, it's easy to update one of the terms in there for a revision of the Higher Rate threshold point and get it wrong. You could type 36400 instead of 34600 as being the new threshold and no-one would notice until you came to do a P35. These are only in-house things though and I check for sane end-product.

What I mean is that Excel formulae can be aesthetically pleasing and it isn't about the simplicity or stability or what you use. Getting the brackets right and knowing what the hell is going on within a formula at the time just give me a sense of fulfillment. It just appealed to me at the time that you could nest IF statements like that.

Thanks (0)
avatar
By listerramjet
18th Feb 2008 12:06

Hi Geoff
OK, your formula has lots of problems - key ones for me are that it hard codes stuff, and a simple error in positioning commas or brackets will cause logic errors. Its a good example of the argument for banning excel!

Thanks (0)
avatar
By Richard Willis
18th Feb 2008 11:57

Let's not forget why people need these ideas
Hi J

I am with Geoff up to a point. However we must not loose track of what people are trying to ultimately achieve, i.e. EARN MONEY. I LOVE to engage in mental excercise; however I rarely have the time while at work. The old adage 'KISS' applies in most cases!

Thanks (0)
avatar
By geoffemtacs
18th Feb 2008 11:07

Get out now!
"Complex Excel formulas are perhaps not the correct route forward in this context"

Yes - but they're so much fun! I actually relish the construction of them without a slightest thought of simplification. Try making sense of this:

=IF(F11<0,0,IF(F11>34600*A11/12,0.18*(INT(F11-(A11*34600)/12))+0.22*INT(F11)-(A11*0.12*2230/12),IF(F11<2230*A11/12,0.1*INT(F11),0.22*INT(F11)-(A11*0.12*2230/12))))

I'm sure I could do this much neater with pivots and lookups, but where's the fun in that when you can do the thing with nested IFs?

Thanks (0)
avatar
By User deleted
18th Feb 2008 10:12

Use Access .......
and do all the selects in SQL.

This would then provide a simple manageable audit trail and the tool is far better suited for this particular use; controlled risk

Richards excellence with Excel is great but let us not forget that the question cane from a person who did not know how to achieve his own requirements.

What happens when the questionner in turn passes this spreadsheet onto one of his collegues who also does not understand the formulas.

There is no right or wrong way of achieving the goal - nevertheless it is all about keeping any solution simple and risk adverse. Complex Excel formulas are perhaps not the correct route forward in this context

Thanks (0)
avatar
By listerramjet
18th Feb 2008 09:06

hi chaps
Richards advice is of course excellent and to the point, and David makes an interesting point about Pivots. Worth noting that the pivot is only dynamic if the key column (first two letters of the account code) are extracted by formula, and when the pivot is refreshed. Otherwise text to columns is an interesting idea.

Depends on how big the data table is as to whether it is relevant, and also what else is going on in the worksheet, but formula as they multiply bring a slowing of the calculations, and an increased risk of error if formula are onitted or overwritten on particular rows

From a data perspective it might be valuable to provide a list of the codes to the user who might have a view if others are relevant. Advanced Filter provides this sort of functionality.

But because the whole thing is starting to become more complex I would suggest VBA to wrap it all up.

Thanks (0)
avatar
By David Carter
17th Feb 2008 10:17

Can't we eliminate SUMIF?
Many thanks to Richard once again, AccountingWEB's unpaid Excel expert.

But it's all these complicated formulas that give Excel a bad name. The obvious answer for J is to create a new field via Text to Columns, then shove the whole lot into a pivot table. Not a formula in sight.

Shouldn't we be trying to ban SUMIF and get people to use pivot tables?

Thanks (0)
avatar
By RichardSchollar
16th Feb 2008 15:37

You're welcome
I learned most of what I know from:

www.mrexcel.com/forum

I should point out I'm a Moderator at the forum above, and there are many other dedicated excel forums, but I happen to really like the one above ;-)

Richard

Thanks (0)
avatar
By User deleted
16th Feb 2008 15:22

Cheers Mate!
Your a star!!! Could do with a hot link to your brain.

Thanks (0)
avatar
By RichardSchollar
16th Feb 2008 13:35

Yup
There are a variety of ways to do this - here's one:

=SUM(SUMIF($A$2:$A$2000,{"MT*","NT*"},$C$2:$C$2000))

Richard

Thanks (0)
avatar
By User deleted
16th Feb 2008 12:43

Can I add in a second criteria?
Thanks Richard, works at treat. Is it possible to add a second wildcard? Example to return the accounts codes beginning with MT & NT

Thanks (0)
avatar
By RichardSchollar
16th Feb 2008 11:45

Use a wildcard
Hi J

You can use a wildcard character in the criteria for this:

=SUMIF($A$2:$A$2000,"MT*",$C$2:$C$2000)

where * means 0 or more of any characters.

Best regards

Richard

Thanks (0)