Formula Question

  • Thread starter Thread starter Bonnie
  • Start date Start date
B

Bonnie

Hi,

Not sure if this will make sense...
This is my DATA FILE
I have a spread sheet that has:
A column with account numbers for region
ie.
A B C
5927 FEI - (084) 20.00
5927 FEI - #084 10.00
5927 FEI - (084) 20.00
5927 F supply co. 10.00

B column with names of various companies
(This column could have mutiple rows of the same company
but it will always have the same account number)
c column has totals

The problem is that the company some times will be
inconsistent with what they call it.... ie. FEI - (084) or
FEI - #084.

What I am doing is:

I have a spread sheet that has some of these account names
and I want to sumif the totals if it finds the name.

The problem lies in that the data is not consistent with
what they call it.

how can I do a sumif say if you find either names or maybe
use a wildcard to be safe. Because it might come over a
totally different way next month.

I hope some out there can help!

thanks, B.
 
Bonnie
Here's a couple of ideas

With company name directly in formula
=SUMIF(B1:B10,"FEI*",C1:C10

With company name in seperate cell
=SUMIF(B1:B10,F2&"*",C1:C10

You can use an ampersand (&) to connect text string

Using company number
=SUMIF(A1:A10,5927,C1:C10

I assume there is some reason preventing you from using the company number but I figured I through it in

Good Luck
Mark Graesse
(e-mail address removed)

----- Bonnie wrote: --

Hi

Not sure if this will make sense..
This is my DATA FIL
I have a spread sheet that has
A column with account numbers for regio
ie
A B
5927 FEI - (084) 20.0
5927 FEI - #084 10.0
5927 FEI - (084) 20.0
5927 F supply co. 10.0

B column with names of various companie
(This column could have mutiple rows of the same company
but it will always have the same account number
c column has total

The problem is that the company some times will be
inconsistent with what they call it.... ie. FEI - (084) or
FEI - #084

What I am doing is

I have a spread sheet that has some of these account names
and I want to sumif the totals if it finds the name

The problem lies in that the data is not consistent with
what they call it

how can I do a sumif say if you find either names or maybe
use a wildcard to be safe. Because it might come over a
totally different way next month

I hope some out there can help

thanks, B
 
Thnx for replying...

Yes your correct the account number is more for regional
use. So there could be 5 stores with the same account
number - and have mutliple lines of the same store because
other columns show what that store purchased.

FEI is pretty common in the name but the store # is unique

can I skip the FEI part and just have it find the store #.
-----Original Message-----
Bonnie,
Here's a couple of ideas:

With company name directly in formula:
=SUMIF(B1:B10,"FEI*",C1:C10)


With company name in seperate cell:
=SUMIF(B1:B10,F2&"*",C1:C10)

You can use an ampersand (&) to connect text string.


Using company number:
=SUMIF(A1:A10,5927,C1:C10)

I assume there is some reason preventing you from using
Click to expand...
the company number but I figured I through it in.
 
Bonnie
I didn't notice the store number the first time I looked at your table. Try one of these formulas

=SUMIF(B1:B10,"*084*",C1:C10

=SUMIF(B1:B10,"*"&F2&"*",C1:C10

For the second formula you would enter the store number in F2. You can change this reference to any cell you prefe

This will look for anything with an 084 in it, so if you also have a store number 1084, it will also be lumped in

----- (e-mail address removed) wrote: ----

Thnx for replying..

Yes your correct the account number is more for regional
use. So there could be 5 stores with the same account
number - and have mutliple lines of the same store because
other columns show what that store purchased

FEI is pretty common in the name but the store # is uniqu

can I skip the FEI part and just have it find the store #
 
Back
Top