Dear All,
my first post. I am revamping an excel spreadsheet that I inherited in my new position.
I want to use Sumif to find account codes then sum all occurences of expenses under that given account code. The catch is that the account codes are four digits: e.g. 5314. I typically have groups of account codes that I would like to sum together: e.g. 5311, 5312, 5313, 5314.
I created this function:
=SUMIF('sheet1'!K14:K449;"531?";'sheet1'!S14:S449)
using the question mark character ? as a wildcard for any of my codes that begin with 531.
This does not work. It does not find and sum the occurences of the 531 codes. In my current data set there are two occurences of 5314 totalling 19,000Euros. If I replace 531? with 5314 for the criteria then I get the correct result.
I guess my question is how to use a wildcard in this case and if it is possible.
Thanks!
Last edited by vlbdirector; 12-01-2011 at 12:11 PM.
Try an asterisk instead of a question mark.
Life is about perseverance. Remember: today's mighty oak tree is just yesterday's nut that held its ground.
If you like a post, please rate it with the scales icon (top right).
If you feel really indebted, please consider a donation to charity, such as Feed the Children or Habitat for Humanity.
The wildcards don't work on numericals here....
Try:
=SUMPRODUCT(--(LEFT('sheet1'!K14:K449,3)+0=531);'sheet1'!S14:S449)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
@forum moderator:Originally Posted by NBVC
thanks for your help. That formula returned the typical error message stating that the formula contains an error but does not say anything else. It did not like the LEFT function. Could you take one more stab at it? I feel like I should pay you for your help.
Maybe in Italian version:
=MATR.SOMMA.PRODOTTO(--(SINISTRA('sheet1'!K14:K449;3)+0=531);'sheet1'!S14:S449)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
@NBVC I am running the Excel 2007 version that we received from the GU main campus. It is English. I tried the Italian version you graciously wrote for me but it did not recognize the syntax.
As an update: This is currently my workaround. It is not elegant and requires lots of cutting an pasting but for my needs is doable. For the most part my account codes are in groups of 3 or maximum 6.
=SUMIF(Data!K14:K449; 5311;Data!S14:S449 )+SUMIF(Data!K14:K449; 5312;Data!S14:S449 )+SUMIF(Data!K14:K449; 5313;Data!S14:S449 )+SUMIF(Data!K14:K449; 5314;Data!S14:S449)
I just add a string of SUMIF functions with discreet criteria (5311, 5312, 5313, 5314). This does work but a wildcard would be nice for numerals... perhaps in a future release of Excel.
Thanks again for you kind attention.
I would think:
=SUMIFS(Data!S14:S449;Data!K14:K449; ">=5311";Data!K14:K449; "<=5314")
Although romperstomper's solution would be for sure the best for this kind of query, especially if you have XL2007 or later... I just had missed replacing one of my commas with a semicolon in my original formula.. so I think this should work too (if you desire the "wildcard" approach).
=SUMPRODUCT(--(LEFT('sheet1'!K14:K449;3)+0=531);'sheet1'!S14:S449)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
@romperstomper,
that one worked. I will move this thread to the solved column. Thank you both for your kind support.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks