How do find text with sumproduct if possible

[ryan.fitzpatrick3]

I have a sumproduct equation which finds the respective numbers on one
sheet and pulls it into a designated location on another sheet. Now
the last variable the "'Pulled from Access'!$G$7:$G$5420)" normally
pulls a number (which makes sense because it a sumproduct formula),
but I now have text in this column, is there away to make this
equation pull a text, or whatever is in that cell over? Maybe this is
not the correct equation, can anyone help me with the correct equation
which has these criteria and pulls text as the end result?

=SUMPRODUCT(--(1*'Pulled from Access'!$B$7:$B$5420=Data!$A11),--
(1*'Pulled from Access'!$C$7:$C$5420=Data!$B11),--(1*'Pulled from
Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1, 2007"))),--
('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2)),--('Pulled from
Access'!$H$7:$H$5420="R"),'Pulled from Access'!$G$7:$G$5420)

The equation basically is stating that if range B ='s (let say Ryan)
and range C ='s age, and range I ='s the month born and range J ='s
the year born and range H ='s right handed, then the G range will find
the correct match for this criteria. (this is an example. This
equation will find the correct match out of a huge list of names,
ages, months, year, etc etc and put it into a specific designated
location on another sheet. It works too, so the question is getting a
text instead of a number over on the G range).

ryan

 

[Bob Phillips]

=INDEX(('Pulled from Access'!$G$7:$G$5420,MATCH(1,
('Pulled from Access'!$B$7:$B$5420=Data!$A11)
*('Pulled from Access'!$C$7:$C$5420=Data!$B11)
*('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1,
2007")))
*('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))
*('Pulled from Access'!$H$7:$H$5420="R"),0))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[ryan.fitzpatrick3]

Didn't work said NA in the formula setup.

=INDEX(('Pulled from Access'!$G$7:$G$5420,MATCH(1,
('Pulled from Access'!$B$7:$B$5420=Data!$A11)
*('Pulled from Access'!$C$7:$C$5420=Data!$B11)
*('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1,
2007")))
*('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))
*('Pulled from Access'!$H$7:$H$5420="R"),0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

 

[ryan.fitzpatrick3]

I did the ctr-shift -enter and the brackets were put onto the formula
but it still says #NA.

=INDEX('Pulled from Access'!D72500,MATCH(1,('Pulled from Access'!$B
$7:$B$5420=Data!$A11)*('Pulled from Access'!$C$7:$C$5420=Data!
$B11)*('Pulled from Access'!$I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&"
1,2007")))*('Pulled from Access'!$J$7:$J$5420=MID($I$8,3,2))*('Pulled
from Access'!$H$7:$H$5420="R"),0))

ryan

 

[Dave Peterson]

Maybe you don't have a row that has all those matches???

If you're positive you do, I'd double check it.

Pick out the row that should be the match (say row 222). Then put these
formulas in empty cells:

='Pulled from Access'!B222=Data!$A11
='Pulled from Access'!C222=Data!$B11
='Pulled from Access'!I222=MONTH(DATEVALUE(Data!D$10&" 1,2007"))
='Pulled from Access'!J222=MID($I$8,3,2)
='Pulled from Access'!H222="R"

If they all return TRUE, then I don't know an answer.

If any return False, you'll know that something isn't the way you expected it.

 

[ryan.fitzpatrick3]

I think the problem is that the row can be any row where ever that
criteria is meet, since the list gets bigger because of data being
inputed. This is why there is a range for the criteria, because the
respective cell that i'm looking for could be in that range. Does the
index and match formula do the same as this sumproduct formula, except
that it looks for text instead of numbers.

=SUMPRODUCT(--(1*$B$7:$B$5420=Data!$A11),--(1*$C$7:$C$5420=Data!
$B11),--(1*$I$7:$I$5420=MONTH(DATEVALUE(Data!E$10&" 1, 2007"))),--($J
$7:$J$5420=MID($I$8,3,2)),--($H$7:$H$5420="R"),$G$7:$G$5420)

The end result of this formula is in the G range where there is a
number. In the F range I have text i.e. first names, so I would like
this equation or whatever equation that finds text to do the same
function as this but list text. I didn't think it would be that
difficult. So I changed it orginally to below to look for the first
names (text).

=SUMPRODUCT(--(1*$B$7:$B$5420=Data!$A11),--(1*$C$7:$C$5420=Data!
$B11),--(1*$I$7:$I$5420=MONTH(DATEVALUE(Data!E$10&" 1, 2007"))),--($J
$7:$J$5420=MID($I$8,3,2)),--($H$7:$H$5420="R"),$F$7:$F$5420)

Now does the index equation do what I'm looking for? Thanks for you
help by the way.

=INDEX(D72500,MATCH(1,($B$7:$B$5420=Data!$A11)*($C$7:$C$5420=Data!
$B11)*($I$7:$I$5420=MONTH(DATEVALUE(Data!D$10&" 1,2007")))*($J$7:$J
$5420=MID($I$8,3,2))*($H$7:$H$5420="R"),0))

ryan

 

[Dave Peterson]

I understand why the formula contains a range--several ranges, in fact.

If you say that there is a match (on the same row) for all those criteria, then
it shouldn't be that difficult to test those formulas I suggested in the
previous post.

What happens when you find the row that matches and use those formulas?

 

[ryan.fitzpatrick3]

I put this equation into a blank cell, it showed false.

='Pulled from Access'!B222=Data!$A11

 

[Dave Peterson]

Then row 222 doesn't have the match.

You were supposed to find the row that looked like the match would work. If you
don't want to try that, then I guess I'm out of ideas.

 

[ryan.fitzpatrick3]

I did try it, I found the right row which was:

='Pulled from Access'!B1250=Data!$A11

It showed up as false still, which I dont know why, it is a match.
sorry for not stating that I did this.

 

[Dave Peterson]

If excel doesn't think it's a match, it's not a match.

What's in the cell?

If it's plain old alpha text, look for extra spaces (leading/trailing/embedded).

(or just a transposition of characters???)

If the value looks like a number, then maybe one of the cells is really a
number, but the other is really text.

You can check each one with:
=isnumber('pulled from access'!b1250)
and
=isnumber(data!$a11)

Since your data is pulled from Access, I bet it's the number/text problem.

To convert text numbers to number numbers:
Select and empty cell
edit|copy
select the offending range
edit|paste special|check values and add

=========

You could change this portion of the formula, too:

('Pulled from Access'!$B$7:$B$5420=Data!$A11&"")

Then data!a11&"" will be treated as text.