+ Reply to Thread
Results 1 to 4 of 4

Countifs,Indirect Function and Vlookup combined

  1. 08-08-2013, 12:39 AM #1
    Yari1986
    Yari1986 is offline
    Registered User
    Join Date
    07-22-2013
    Location
    Abu dhabi
    MS-Off Ver
    MS 2003
    Posts
    5

    Countifs,Indirect Function and Vlookup combined

    Hi ,

    Need your help on how I can combine the countif, indirect function and vlookup functions into 1 cell.
    I have several sheet for each month, and 1 summary tab. I need to get the total number of VL for a specific employee, and for a specific month to be reflected in the summary tab, and data should automatically change everytime I change the month in cell C1(indirect function).

    Also, if the employee is not in 1 of the sheets the summary tab should not get any error message.
    I attempted many times, but just couldn't get it right....
    Hope anyone can help me please....
    Thanks in advance!!


    For the month of July

    VL Sick Leave Half Day Vacation Leave
    Alcantara, Pia 0 4 1
    Espiritu,Russ 0 4
    Gonzalo,Glenn 0 0
    Malsom, Jonalyne 0 0 1
    Napoles,Kristen 0 0 VL
    Viduya,Kristel 10 0 VL
    Antipuesto,Carlos 0 0 VL
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-08-2013, 01:03 AM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Countifs,Indirect Function and Vlookup combined

    Hi and welcome to the forum

    1st, remove that extra row in July to make all month tab layouts identical

    then, add an extra row in Summary, under your titles (that will become row 5, you can hide this is you want) and enter just the codes that you use on the month tabs.

    Then in C6, copied down and across, use this...
    =IF(COUNTIF(INDIRECT($C$1&"!$C"&ROW()-3&":$AG"&ROW()-3),C$5)=0,"",COUNTIF(INDIRECT($C$1&"!$C"&ROW()-3&":$AG"&ROW()-3),C$5))

    If you are OK with showing 0 in the table (there will not be errors using countif), then just use this, copied down and across...
    =COUNTIF(INDIRECT($C$1&"!$C"&ROW()-3&":$AG"&ROW()-3),C$5)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 08-08-2013, 02:54 AM #3
    Yari1986
    Yari1986 is offline
    Registered User
    Join Date
    07-22-2013
    Location
    Abu dhabi
    MS-Off Ver
    MS 2003
    Posts
    5

    Re: Countifs,Indirect Function and Vlookup combined

    Works perfect!!
    Thank you so much, this is a Big Help!!
    Reply With QuoteRegister To Reply
  4. 08-08-2013, 11:19 AM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Countifs,Indirect Function and Vlookup combined

    Happy to help
    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)

Similar Threads

  1. Indirect function combined with Array
    By NYRealEstateAnalyst in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-10-2013, 02:46 AM
  2. Excel 2007 : More efficient: 2 long COUNTIFS or 1 COUNTIFS and 1 VLOOKUP based on the COUNTIFS?
    By dip11 in forum Excel General
    Replies: 0
    Last Post: 12-16-2011, 09:01 AM
  3. Vlookup, If statement and Indirect combined function help
    By ms_tee in forum Excel General
    Replies: 2
    Last Post: 10-30-2011, 05:15 PM
  4. VLOOKUP combined with if function
    By Tina B in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-24-2009, 10:23 AM
  5. [SOLVED] Combined VLOOKUP AND BETWEEN FUNCTION
    By Louis Markowski in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-13-2005, 11:05 AM

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