Formula that will recongize text

S

sweetsue516

I have a spreadsheet with one page for my report and another page that holds
my data that is updated on a regular basis so I automated it.

The problem is my formula only recognizes numbers and the data contains
numbers and text. I use sumif because there is not any repeated data.

Here is my formula:

=SUMIF('Morning Star '!$A$1:$A$25,$N$3,'Morning Star '!B$1:B$26)
 
S

Sandy Mann

=SUMIF('Morning Star '!$A$1:$A$25,$N$3,'Morning Star '!B$1:B$26)
The problem is my formula only recognizes numbers and the data contains
numbers and text.
Click to expand...

You can't SUM text so I do not understand what it is that you are trying to
do. Can you describe what it is gthat you want to do in other words?


--
Regdads


Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk
 
S

sweetsue516

Sandy,

I need the formula to tell me what is in the cell.

Example:

On sheet one Column A1:25 list my funds. Column B1:25 list their
performance for the past month.

On sheet two I download the information to update the performance on a
regular basis.

The sumif formula looks up my fund on sheet one then looks it up on sheet
two to load the performance on sheet one.

Let me know if I explained this well enough or not and thank you for your
help.
 
S

Sandy Mann

Hi sweetsue,

Mmmm...... I am not posh enough to have a list of funds <g> but let us
see:

I am assuming that:
sheet two is named "Morning Star"

the list of fund names in sheet one is in A1:A25
the list of transferred updates is in sheet one in B1:B25

'Morning Star' also has a list of funds in A1:A25
'Morning Star' has the downloaded updates in B1:B25
the updates are text values like +30 or -50 etc

You wish to transfer the latest updates from 'Morning Star' B1:B25 to sheet
one B1:B25

I the above is correct then I would use INDEX/MATCH in sheet one:

=--INDEX('Morning Star'!$B$1:$B$25,MATCH(A1,'Morning Star'!$A$1:$A$25,0))
and copy it down from B1 to B25

This looks down 'Morning Star' A1:A25 to find a Matching name with sheet one
cell A1, then indexes down 'Morning Star' B1:B25 the same number of cells to
get the update. The update will be a text value and so to change it onto a
number I have added a double negative at the start to convert the text
number into a number.

If you want the value of the fund B1:B25 to be automatically updated by the
amount of update in 'Morning Star' B1:B25 then set Iteration to 1 and use
the formula:

=B1+(--INDEX('Morning Star'!$B$1:$B$25,MATCH(A1,'Morning
Star'!$A$1:$A$25,0)))
and copy it down B1:B25

If I am completely off the wall then post back and perhapd one of our posh
friend who look after their own funds in Excel will jump in with a better
solution.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk

Oh yes! Always try something new on a COPY of your spreadsheet.........
just in case!
 
S

Sandy Mann

=B1+(--INDEX('Morning Star'!$B$1:$B$25,MATCH(A1,'Morning
Star'!$A$1:$A$25,0)))
and copy it down B1:B25
Click to expand...

No! I wouldn't do that because it will update every time that the workbook
recalculates.

I'm not really sure that I understand exactly what it isn you want to do.
Can you explain a bit more please?
Do you want to download the updates and have the value of the funds
automatically change?

--
Regards

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk
 
R

Rick

Sweetsue,

if the text in N3 is the same as the text in A1:A25, then you shouldn't have
a problem. Obviously, if there is text in B1:B25 then the sum won't work so
you need to extract the text out of the performance values.

Can you provide an example of the data on the Morning Star sheet and what is
in field N3?

Rick
 
D

David Biddulph

And while you're at it, make sure that the B array is the same length as the
A array. At the moment, one is 25 long and the other 26.
 
Top