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.

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

what are you actually trying to acheive?
--
paul

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.

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

=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.

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

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

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

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.

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

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.

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

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.

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

Hi

I have tried that one, and it still doesnt appear to be pulling the
cell contents in. It is only pulling in the cells with one specific
variant in it, i.e. 8500/8550. It wont pull 2424, or 8400 or
8400/8500/8550

Toppers wrote:
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.