Help on Sumproduct
 

Col A ColB ColC ColD ColE
Status RoomNo Name Start Paid
Blank 1 James 01-Jan-09 2500
U 1 Anne 01-Aug-09 3000
U 2 Jill 01-Feb-09 2500
U 3 Maggie 01-Feb-09 2500
Blank 4 Rob 01-07-09 3000
U 4 Mike 01-05-09 2000
Blank 5 Sally 01-02-09 1500

In Col A I have Status Blank means he is not staying in the room
"U" means this room is in use by the particular person

I have another tab in the same file with foll format
Room no Name Start Paid
1
2
3
4
5

I want to get the names of the person in this summary

I used sumproduct((status="U")*(RoomNo="A2"),(Start)) to get Dates it works
to get me the start date and also the amount paid....

But I want to get the names with this method sumproduct does not work
because the names are text !!! any help please

Final work should look this way
Room Name Start Paid
1 Anne 01-Aug-09 3000
2 Jill 01-Feb-09 2500
3 Maggie 01-Feb-09 2500
4 Mike 01-05-09 2000
5

Help on Sumproduct
 

Try the formula press CTRL+SHIFT+ENTER to enter the formula

=INDEX(name,MATCH(1,(start=C2)*(paid=D2),0))

C2 = summary table startdate
D2 = summary table paid

If this post helps click Yes
---------------
Jacob Skaria


"claude jerry" wrote:

Col A ColB ColC ColD ColE
Status RoomNo Name Start Paid
Blank 1 James 01-Jan-09 2500
U 1 Anne 01-Aug-09 3000
U 2 Jill 01-Feb-09 2500
U 3 Maggie 01-Feb-09 2500
Blank 4 Rob 01-07-09 3000
U 4 Mike 01-05-09 2000
Blank 5 Sally 01-02-09 1500

In Col A I have Status Blank means he is not staying in the room
"U" means this room is in use by the particular person

I have another tab in the same file with foll format
Room no Name Start Paid
1
2
3
4
5

I want to get the names of the person in this summary

I used sumproduct((status="U")*(RoomNo="A2"),(Start)) to get Dates it works
to get me the start date and also the amount paid....

But I want to get the names with this method sumproduct does not work
because the names are text !!! any help please

Final work should look this way
Room Name Start Paid
1 Anne 01-Aug-09 3000
2 Jill 01-Feb-09 2500
3 Maggie 01-Feb-09 2500
4 Mike 01-05-09 2000
5

Help on Sumproduct
 

I did a small modification to your formula

index(Name,Match(1,(Status="U")*(RoomNo=A2),0)) CTRL+SHIFT+ENTER ans It worked


"Jacob Skaria" wrote:

Try the formula press CTRL+SHIFT+ENTER to enter the formula

=INDEX(name,MATCH(1,(start=C2)*(paid=D2),0))

C2 = summary table startdate
D2 = summary table paid

If this post helps click Yes
---------------
Jacob Skaria


"claude jerry" wrote:

Col A ColB ColC ColD ColE
Status RoomNo Name Start Paid
Blank 1 James 01-Jan-09 2500
U 1 Anne 01-Aug-09 3000
U 2 Jill 01-Feb-09 2500
U 3 Maggie 01-Feb-09 2500
Blank 4 Rob 01-07-09 3000
U 4 Mike 01-05-09 2000
Blank 5 Sally 01-02-09 1500

In Col A I have Status Blank means he is not staying in the room
"U" means this room is in use by the particular person

I have another tab in the same file with foll format
Room no Name Start Paid
1
2
3
4
5

I want to get the names of the person in this summary

I used sumproduct((status="U")*(RoomNo="A2"),(Start)) to get Dates it works
to get me the start date and also the amount paid....

But I want to get the names with this method sumproduct does not work
because the names are text !!! any help please

Final work should look this way
Room Name Start Paid
1 Anne 01-Aug-09 3000
2 Jill 01-Feb-09 2500
3 Maggie 01-Feb-09 2500
4 Mike 01-05-09 2000
5