Any Formula like Sumifs for Text Data

[Amit Punia]

Hi,

In the attached file there are two sheets i.e. "Feb 2016" & "Advisors". In sheet "Feb 2016", cell E3 - I want a formula which takes the reference from sheet "Advisors" and provide Login Time on date Feb 2, 2016 on the basis of "User Name".
So the output in column E3(Login time of advisor) & F3(Log out time of advisor) should be 11:16 AM & 9:33 PM respectively. In other word I can say that the output formula in cell E3 is showing the login time of deep on feb 2, 2016 and answer is 11:16 PM.

Kindly help ASAP.

 

[Khalid NGO]

Hi Amit,

First I thought about simple SUMIFS:
=SUMIFS(Advisors!$C$2:$C$3735,Advisors!$A$2:$A$3735,E$1,Advisors!$B$2:$B$3735,$A3)

But the answer did not matched with your expected output, as there are duplicate entries.

I am not sure about the duplicate entries, can you try the following regular formula in E3 and copy down: ?

=INDEX(Advisors!$C$2:$D$3735,MATCH($A3&E$1,INDEX(Advisors!$B$2:$B$3735&Advisors!$A$2:$A$3735,,),0),MATCH(E$2,Advisors!$C$1:$D$1,0))


Few things need to update in your sheets so they can be easily matched:

Advisor sheet:
C1 change the Text to In
D1 change the Text to Out

Feb 2016 sheet:
Do not use merge cells, they always create problems,
Un-merge the header row, where you have mentioned the dates.
Enter dates in both cells (for example 01-Feb-2016 in E1 and F1)

Regards,

 

[Naresh Yadav]

pls try this one with completing the Formula CTRL+SHIFT+ENTER

=INDEX(Advisors!$C$2:$C$3735,MAX(IF(Advisors!$B$2:$B$3735='Feb 2016'!A3,IF(Advisors!$A$2:$A$3735='Feb 2016'!$E$1,ROW(Advisors!$A$2:$A$3735)-1))))

 

[Amit Punia]

Thank you Khalid & Naresh, the formula is working absolutely fine. Thanx a lot