Problem with formula/doesn't pull data with slash (/)

Hi,

I was wondering whether someone could give me a hand with this formula,
or whats wrong with this formula:

=sumif('Parts&Product Numbers'!D$3:D$500,Stock!A32,'Parts&Product
numbers'!E$3:E$500)

The formula works, but it doesnt pull the information i want in column
e (at the end of the formula). In column E i have three variables:

8400
8500/8550
8400/8500/8550

the formula only returns the 8400 variable, it doesn't seem to like the
/ , or any other form of punctuation. Please could someone tell me how
it can return these variables with their attached /.

thank you

Greg.

what are you actually trying to acheive?
--
paul
[email protected]
remove nospam for email addy!



"Greg" wrote:

> Hi,
>
> I was wondering whether someone could give me a hand with this formula,
> or whats wrong with this formula:
>
> =sumif('Parts&Product Numbers'!D$3:D$500,Stock!A32,'Parts&Product
> numbers'!E$3:E$500)
>
> The formula works, but it doesnt pull the information i want in column
> e (at the end of the formula). In column E i have three variables:
>
> 8400
> 8500/8550
> 8400/8500/8550
>
> the formula only returns the 8400 variable, it doesn't seem to like the
> / , or any other form of punctuation. Please could someone tell me how
> it can return these variables with their attached /.
>
> thank you
>
> Greg.
>
>

=SUMIF (SUM) will ignore the data with / as it treats it as non-numeric.

Do you actual want to SUM these numbers or return them as a string?

Given (only) the three cells below, what do you want returned?

8400
8500/8550
8400/8500/8550


"Greg" wrote:

> Hi,
>
> I was wondering whether someone could give me a hand with this formula,
> or whats wrong with this formula:
>
> =sumif('Parts&Product Numbers'!D$3:D$500,Stock!A32,'Parts&Product
> numbers'!E$3:E$500)
>
> The formula works, but it doesnt pull the information i want in column
> e (at the end of the formula). In column E i have three variables:
>
> 8400
> 8500/8550
> 8400/8500/8550
>
> the formula only returns the 8400 variable, it doesn't seem to like the
> / , or any other form of punctuation. Please could someone tell me how
> it can return these variables with their attached /.
>
> thank you
>
> Greg.
>
>

Thanks for you reply.

In column E there could be those three variables all the way down and
intechangeable eg.

E3 = 8400
E4= 8400/8500
E5-= 8400
E6= 8400/8500/8550
etc etc etc

So my formula now (the one I wrote before), works. But, it only pulls
in 8400. I want it to pull in whatever is in that cell. It doesn't
appear to like 8400/8500 or 8400/8500/8550. If it is either one of the
two variables with a slash it returns 0. when it should theoretically
recall exactly what is in that cell (in the E column).

Is that any clearer?

Cheers

I guess I am not really askign it to sum but to pull that specific
cell's entry, so yes the string would be good, as long as it returns
exactly what is in that cell.

Cheers,


Toppers wrote:
> =SUMIF (SUM) will ignore the data with / as it treats it as non-numeric.
>
> Do you actual want to SUM these numbers or return them as a string?
>
> Given (only) the three cells below, what do you want returned?
>
> 8400
> 8500/8550
> 8400/8500/8550
>
>
> "Greg" wrote:
>
> > Hi,
> >
> > I was wondering whether someone could give me a hand with this formula,
> > or whats wrong with this formula:
> >
> > =sumif('Parts&Product Numbers'!D$3:D$500,Stock!A32,'Parts&Product
> > numbers'!E$3:E$500)
> >
> > The formula works, but it doesnt pull the information i want in column
> > e (at the end of the formula). In column E i have three variables:
> >
> > 8400
> > 8500/8550
> > 8400/8500/8550
> >
> > the formula only returns the 8400 variable, it doesn't seem to like the
> > / , or any other form of punctuation. Please could someone tell me how
> > it can return these variables with their attached /.
> >
> > thank you
> >
> > Greg.
> >
> >

Greg wrote:

> I guess I am not really askign it to sum but to pull that specific
> cell's entry, so yes the string would be good, as long as it returns
> exactly what is in that cell.
>
> Cheers,
>
>
> Toppers wrote:
> > =SUMIF (SUM) will ignore the data with / as it treats it as non-numeric.
> >
> > Do you actual want to SUM these numbers or return them as a string?
> >
> > Given (only) the three cells below, what do you want returned?
> >
> > 8400
> > 8500/8550
> > 8400/8500/8550
> >
> >
> > "Greg" wrote:
> >
> > > Hi,
> > >
> > > I was wondering whether someone could give me a hand with this formula,
> > > or whats wrong with this formula:
> > >
> > > =sumif('Parts&Product Numbers'!D$3:D$500,Stock!A32,'Parts&Product
> > > numbers'!E$3:E$500)
> > >
> > > The formula works, but it doesnt pull the information i want in column
> > > e (at the end of the formula). In column E i have three variables:
> > >
> > > 8400
> > > 8500/8550
> > > 8400/8500/8550
> > >
> > > the formula only returns the 8400 variable, it doesn't seem to like the
> > > / , or any other form of punctuation. Please could someone tell me how
> > > it can return these variables with their attached /.
> > >
> > > thank you
> > >
> > > Greg.
> > >
> > >

Try VLOOKUP:

=VLOOKUP(Stock!A32,'Parts&Product Numbers'!D$3:E$500,2,0)

Will match A32 against D3:D500 and pull matched value i.e same row, from
E3:E500.

If there is more than one match, it will only retrieve first one it finds.

HTH

"Greg" wrote:

> I guess I am not really askign it to sum but to pull that specific
> cell's entry, so yes the string would be good, as long as it returns
> exactly what is in that cell.
>
> Cheers,
>
>
> Toppers wrote:
> > =SUMIF (SUM) will ignore the data with / as it treats it as non-numeric.
> >
> > Do you actual want to SUM these numbers or return them as a string?
> >
> > Given (only) the three cells below, what do you want returned?
> >
> > 8400
> > 8500/8550
> > 8400/8500/8550
> >
> >
> > "Greg" wrote:
> >
> > > Hi,
> > >
> > > I was wondering whether someone could give me a hand with this formula,
> > > or whats wrong with this formula:
> > >
> > > =sumif('Parts&Product Numbers'!D$3:D$500,Stock!A32,'Parts&Product
> > > numbers'!E$3:E$500)
> > >
> > > The formula works, but it doesnt pull the information i want in column
> > > e (at the end of the formula). In column E i have three variables:
> > >
> > > 8400
> > > 8500/8550
> > > 8400/8500/8550
> > >
> > > the formula only returns the 8400 variable, it doesn't seem to like the
> > > / , or any other form of punctuation. Please could someone tell me how
> > > it can return these variables with their attached /.
> > >
> > > thank you
> > >
> > > Greg.
> > >
> > >
>
>