x
Solved

I need an excel formula

Posted on 2011-03-24
Medium Priority
231 Views
I am trying to reference a range of cells to look for a specific persons name. For example, if I had Agents 1 though 10. Instead of doing 10 seperate formulas. Look at the very last part of the formula .

=SUMPRODUCT(((SRDATA!\$H\$2:\$H\$10000>=\$A114)*(SRDATA!\$H\$2:\$H\$10000<=\$B114)+(SRDATA!\$I\$2:\$I\$10000>=\$A114)*(SRDATA!\$I\$2:\$I\$10000<=\$B114)+(SRDATA!\$M\$2:\$M\$10000>=\$A114)*(SRDATA!\$M\$2:\$M\$10000<=\$B114)>0)*(SRDATA!\$K\$2:\$K\$10000="N")*(SRDATA!\$C\$2:\$C\$10000=A21:A49)).

It isnt working and I need to be able to do something like that or I will have a 28 entry sumproduct formula.
0
Question by:wrt1mea
• 2

LVL 33

Expert Comment

ID: 35211008
Could you post a sample sheet please?
0

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 35211059
You can do this for the last part

ISNUMBER(MATCH(SRDATA!\$C\$2:\$C\$10000,A21:A49,0))

so the whole formula becomes this

=SUMPRODUCT(((SRDATA!\$H\$2:\$H\$10000>=\$A114)*(SRDATA!\$H\$2:\$H\$10000<=\$B114)+(SRDATA!\$I\$2:\$I\$10000>=\$A114)*(SRDATA!\$I\$2:\$I\$10000<=\$B114)+(SRDATA!\$M\$2:\$M\$10000>=\$A114)*(SRDATA!\$M\$2:\$M\$10000<=\$B114)>0)*(SRDATA!\$K\$2:\$K\$10000="N")*ISNUMBER(MATCH(SRDATA!\$C\$2:\$C\$10000,A21:A49,0)))

regards, barry

0

LVL 1

Author Comment

ID: 35211060
See attached!

Look at the info tab
3-24-11-part-2.xlsx
0

LVL 1

Author Closing Comment

ID: 35211087
Fantastic! Helped speed up the computations as well!
0

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.