+ Reply to Thread
Results 1 to 19 of 19

V Look & CounIF Fucntions

Hybrid View

  1. 04-15-2013, 05:54 AM #1
    akbar
    akbar is offline
    Forum Contributor
    Join Date
    02-04-2008
    MS-Off Ver
    Microsoft Office 2007
    Posts
    261

    V Look & CounIF Fucntions

    Hi All,

    Please find the attached file.

    On the summary page,if the Job No (D3:D7) & Emp No (B3) equals to the Job No (D4:D13,H4:H13,L4:L13,P4:P13,T4:T13) & emp No
    which is B4:B13 in the worksheet page,then it should count 'P' 'AL' 'EL' 'OL' 'SL' 'OT' respective to the job no.

    Eg:For Emp No 1200,Job No-141 =Total Present days from 1 to 5 is 1,SO P=1,Same Employee on job No-143=Total leave is 2,SO OL=2.

    Hope you guys understand what i mean and my end result should be.

    Thanks,
    Ak
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-15-2013, 08:07 AM #2
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: V Look & CounIF Fucntions

    Hi ak

    Can you pls type manually some expected results in Summary sheet? I am not able to follow you..
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  3. 04-15-2013, 08:27 AM #3
    akbar
    akbar is offline
    Forum Contributor
    Join Date
    02-04-2008
    MS-Off Ver
    Microsoft Office 2007
    Posts
    261

    Re: V Look & CounIF Fucntions

    Fotis please find the attached.

    Manually i have put some values for emp no 1200 on the summary page.

    Hope it helps you understand.

    Thanks,
    Ak
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 04-15-2013, 09:03 AM #4
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: V Look & CounIF Fucntions

    Do you have only 5 dates in your real workbook? If yes we could do using 5 SUMPRODUCTS, or else is more complicated..

    =SUMPRODUCT((Worksheet!$B$4:$B$13=$B$3)*(Worksheet!$D$4:$D$13=$D4)*(Worksheet!$E$4:$E$13=E$2))+SUMPRODUCT((Worksheet!$B$4:$B$13=$B$3)*(Worksheet!$H$4:$H$13=$D4)*(Worksheet!$I$4:$I$13=E$2))+.............
    Reply With QuoteRegister To Reply
  5. 04-15-2013, 09:35 AM #5
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: V Look & CounIF Fucntions

    .........................
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 04-15-2013, 12:59 PM #6
    akbar
    akbar is offline
    Forum Contributor
    Join Date
    02-04-2008
    MS-Off Ver
    Microsoft Office 2007
    Posts
    261

    Re: V Look & CounIF Fucntions

    Thanks for your help Fotis.. Obviously i Have 31 dates on my real work book..

    Any Suggestions..

    Regards,
    Ak
    Reply With QuoteRegister To Reply
  7. 04-16-2013, 04:22 AM #7
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: V Look & CounIF Fucntions

    Not able to find a way for this except of multiply SUMPRODUCT, or Many helper columns... Sorry.
    Reply With QuoteRegister To Reply
  8. 04-16-2013, 06:54 AM #8
    akbar
    akbar is offline
    Forum Contributor
    Join Date
    02-04-2008
    MS-Off Ver
    Microsoft Office 2007
    Posts
    261

    Re: V Look & CounIF Fucntions

    Anyway Thanks Fotis...
    Reply With QuoteRegister To Reply
  9. 04-16-2013, 07:00 AM #9
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: V Look & CounIF Fucntions

    I am really curious to see if someone else has a good idea for this.
    Reply With QuoteRegister To Reply
  10. 04-16-2013, 07:51 AM #10
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: V Look & CounIF Fucntions

    PL see the attached file with formula which can be extended to any number of days.

    Clarifications are most welcome.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 04-16-2013 at 08:42 AM.
    Reply With QuoteRegister To Reply
  11. 04-16-2013, 08:01 AM #11
    akbar
    akbar is offline
    Forum Contributor
    Join Date
    02-04-2008
    MS-Off Ver
    Microsoft Office 2007
    Posts
    261

    Re: V Look & CounIF Fucntions

    It Really Helps,But in the summary sheet columns "J" & "K" which is OT and H-oT is numbers & i need the sum of those...

    Regards,
    Ak
    Reply With QuoteRegister To Reply
  12. 04-16-2013, 08:54 AM #12
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: V Look & CounIF Fucntions

    Pl see the attached file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  13. 04-16-2013, 01:05 PM #13
    akbar
    akbar is offline
    Forum Contributor
    Join Date
    02-04-2008
    MS-Off Ver
    Microsoft Office 2007
    Posts
    261

    Re: V Look & CounIF Fucntions

    Thx Srinivas it worked like charm..

    Regards,
    Ak
    Reply With QuoteRegister To Reply
  14. 04-17-2013, 12:30 AM #14
    akbar
    akbar is offline
    Forum Contributor
    Join Date
    02-04-2008
    MS-Off Ver
    Microsoft Office 2007
    Posts
    261

    Re: V Look & CounIF Fucntions

    Dear Srinivas could you please tell what this really mean.

    6*INT((ROW()-3)/6)))

    Regards,
    Ak
    Reply With QuoteRegister To Reply
  15. 04-17-2013, 01:39 AM #15
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: V Look & CounIF Fucntions

    6*INT((ROW()-3)/6)))

    hi,
    this part of formula is used to select the value in B column Of Summary Sheet.Till the 8th row it selects B3. From 9th Row it selects B9.
    Reply With QuoteRegister To Reply
  16. 04-17-2013, 01:48 AM #16
    akbar
    akbar is offline
    Forum Contributor
    Join Date
    02-04-2008
    MS-Off Ver
    Microsoft Office 2007
    Posts
    261

    Re: V Look & CounIF Fucntions

    Thanks Srinivas,Just few more doubts,suppose If i have 1st emp no till B12 (Now its up to B8) then how this
    "6*INT((ROW()-3)/6)))" formula will be,because i didn't fully understand the concept of this formula.

    Thanks.
    Ak
    Reply With QuoteRegister To Reply
  17. 04-17-2013, 02:51 AM #17
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: V Look & CounIF Fucntions

    THE DIFFERENCEIiN ROWS BETWEEN 1200 AND 1201 IS 6, So 6 is used in Formula.

    If Difference is 12 Replace 6 By 12.
    Reply With QuoteRegister To Reply
  18. 04-17-2013, 05:10 AM #18
    akbar
    akbar is offline
    Forum Contributor
    Join Date
    02-04-2008
    MS-Off Ver
    Microsoft Office 2007
    Posts
    261

    Re: V Look & CounIF Fucntions

    Thanks Srinivas..
    Reply With QuoteRegister To Reply
  19. 04-17-2013, 06:26 AM #19
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: V Look & CounIF Fucntions

    Welcome .Pl mark the thread SOLVED.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1