Sumif with mid function

**ccraig61** · 05-03-2012, 04:11 PM

Hi,

Can anyone tell methe best possible way to sum up specific lines and not others?

I have a spreadsheet that contains a long account string for hundreds of locations, each geographic location starts with a general number and then a specific location number, all in the same cell. I need to sum up the revenues for 3 specific revenue lines (out of 25 types of revenues) but I also need to back out certain brands out of these 3 revenue buckets. I'd like to create a formula to do this instead of manually manipulating the spreadsheet.

Basically, this is what it looks like:

NorthEast: Gas Revenue Snack Revenue
100-123456-100000 3000.00 6600.00
100-234567-100000 10000.00

MidAtlantic
200-123456-100000 5500.00 7900.00
200-234567-100000 10000.00 8000.00

SouthEast
300-123456-100000 4900.00 4800.00

MidWest
400-123456-100000 8500.00 12000.00

Mind you, each of these geographic locations may have 100's of different stores, so this is just a very simple way of presenting this issue.

The revenue buckets will total at the end. Now I need to back out the MidAtlantic's snack revenue. How can I do a sumif function based on the first three numbers of each account string? I could put in a mid formula to pull out the info I need to do this calculation but I don't want to do extra work if it's not necessary.

If anyone can advise me I would greatly appreciate it.

Thanks

**NBVC** · 05-03-2012, 04:24 PM

You can use wildcards with SUMIF...

e.g.

=SUMIF(A:A,"100-*",B:B) this will sum column B based on column A starting with 100-

**ccraig61** · 05-03-2012, 04:31 PM

THANKS! It works beautifully! I just have to be careful as "100" appears in every string. This works for me!

**NBVC** · 05-03-2012, 04:32 PM

The formula I gave, only takes the ones that start with 100 followed by dash (note the * is only after the 100-)... so the ones that have 100 in the middle won't get captured.