+ Reply to Thread
Results 1 to 12 of 12

Which formula to use for Resource Incentive Sheet

  1. 10-31-2012, 11:45 AM #1
    meilieng
    meilieng is offline
    Registered User
    Join Date
    10-30-2012
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    11

    Red face Which formula to use for Resource Incentive Sheet

    Hi, guys. I need help determing which formula is best for columns B, C, D on the Resourcer 1 tab. Essentially, I need the columns to search the 'Resourcer Incentive sheet' tab for Resourcer 1's submitted, interviewed, and placed candidates; and sum them in the respective columns for the corresponding dates. Any suggestions you might have would be greatful. I am assuming I will need a combination of a Vlookup, and if/then formulas, but every combination I have tried does not work. Thanks, in advance for your help on this. Have a great day!
    Attached Files Attached Files
    Last edited by meilieng; 10-31-2012 at 01:52 PM.
    Reply With QuoteRegister To Reply
  2. 10-31-2012, 11:59 AM #2
    djapigo
    djapigo is offline
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Which formula to use for Resource Incentive Sheet

    Hi meileing,

    Try this formula in cell B5...
    =SUMIFS('Resourcer Incentive Sheet'!$H$6:$H$66,'Resourcer Incentive Sheet'!$F$6:$F$66,'Resourcer 1'!B$4,'Resourcer Incentive Sheet'!$E$6:$E$66,"Resourcer 1")

    Then copy to all 3 columns...

    Let me know if this doesn't work...

    Dennis

    re-post: sorry I forgot about the dates criteria...
    =SUMIFS('Resourcer Incentive Sheet'!$H$6:$H$66,'Resourcer Incentive Sheet'!$E$6:$E$66,"Resourcer 1",'Resourcer Incentive Sheet'!$F$6:$F$66,'Resourcer 1'!B$4,'Resourcer Incentive Sheet'!$G$6:$G$66,'Resourcer 1'!$A5)
    Last edited by djapigo; 10-31-2012 at 12:02 PM.
    Reply With QuoteRegister To Reply
  3. 10-31-2012, 12:10 PM #3
    meilieng
    meilieng is offline
    Registered User
    Join Date
    10-30-2012
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Which formula to use for Resource Incentive Sheet

    Oh yes. This is great. I forgot to add one thing. I need it to count the number of times the formula identifies submitted, interviewed, or placed; so that the formula can reflect the number of candidates submitted, interviewed, or placed for a given date. Thanks so much. That formula works great.
    Reply With QuoteRegister To Reply
  4. 10-31-2012, 12:15 PM #4
    djapigo
    djapigo is offline
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Which formula to use for Resource Incentive Sheet

    Hi meilieng,

    Instead of SUMIFS, try COUNTIFS...

    re-post...

    Instead of SUMIFS, try COUNTIFS without the first parameter...

    =COUNTIFS('Resourcer Incentive Sheet'!$F$6:$F$66,'Resourcer 1'!B$4,'Resourcer Incentive Sheet'!$E$6:$E$66,"Resourcer 1")

    So, if you want it combined, maybe something like this....
    =SUMIFS('Resourcer Incentive Sheet'!$H$6:$H$66,'Resourcer Incentive Sheet'!$F$6:$F$66,'Resourcer 1'!B$4,'Resourcer Incentive Sheet'!$E$6:$E$66,"Resourcer 1")&" ("&COUNTIFS('Resourcer Incentive Sheet'!$F$6:$F$66,'Resourcer 1'!B$4,'Resourcer Incentive Sheet'!$E$6:$E$66,"Resourcer 1")&")"

    Sorry, I keep pasting the wrong formula...
    Last edited by djapigo; 10-31-2012 at 12:21 PM.
    Reply With QuoteRegister To Reply
  5. 10-31-2012, 12:24 PM #5
    djapigo
    djapigo is offline
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Which formula to use for Resource Incentive Sheet

    I did it again... I copied the wrong formula without the dates...

    =COUNTIFS('Resourcer Incentive Sheet'!$E$6:$E$66,"Resourcer 1",'Resourcer Incentive Sheet'!$F$6:$F$66,'Resourcer 1'!B$4,'Resourcer Incentive Sheet'!$G$6:$G$66,'Resourcer 1'!$A5)

    or

    =SUMIFS('Resourcer Incentive Sheet'!$H$6:$H$66,'Resourcer Incentive Sheet'!$E$6:$E$66,"Resourcer 1",'Resourcer Incentive Sheet'!$F$6:$F$66,'Resourcer 1'!B$4,'Resourcer Incentive Sheet'!$G$6:$G$66,'Resourcer 1'!$A5)&" ("&COUNTIFS('Resourcer Incentive Sheet'!$E$6:$E$66,"Resourcer 1",'Resourcer Incentive Sheet'!$F$6:$F$66,'Resourcer 1'!B$4,'Resourcer Incentive Sheet'!$G$6:$G$66,'Resourcer 1'!$A5)&")"
    Reply With QuoteRegister To Reply
  6. 10-31-2012, 12:45 PM #6
    djapigo
    djapigo is offline
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Which formula to use for Resource Incentive Sheet

    Hi meilieng,

    I assume you want to copy this formula to the other worksheets (Resource 2, Resource 3, etc.), but be careful...

    If you look at the formula carefully, you can change "Resource 1" (the 3rd entry in the SUMIFS formula and the 2nd entry in the COUNTIFS formula) to refer to cell C1 (make sure to use absolute reference, i.e. $C$1). Then type 'Resource 1' in cell C1.

    Or you can use this formula to get the tab name...
    =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

    Hope these options will help you out...
    Reply With QuoteRegister To Reply
  7. 10-31-2012, 12:47 PM #7
    meilieng
    meilieng is offline
    Registered User
    Join Date
    10-30-2012
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Which formula to use for Resource Incentive Sheet

    Thank you sooooo much!!!!
    Reply With QuoteRegister To Reply
  8. 10-31-2012, 01:01 PM #8
    djapigo
    djapigo is offline
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Which formula to use for Resource Incentive Sheet

    No problem... please make sure to close out this thread by going to your first entry and changing the status to SOLVED...

    Thank you!
    Reply With QuoteRegister To Reply
  9. 10-31-2012, 01:10 PM #9
    meilieng
    meilieng is offline
    Registered User
    Join Date
    10-30-2012
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Which formula to use for Resource Incentive Sheet

    For some reason, I am not seeing that option.
    Reply With QuoteRegister To Reply
  10. 10-31-2012, 01:25 PM #10
    djapigo
    djapigo is offline
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Which formula to use for Resource Incentive Sheet

    Sorry, try this...

    To mark your thread solved do the following:
    - Go to the first post
    - Click edit
    - Click Advance
    - Just below the word "Title:" you will see a dropdown with the word No prefix.
    - Change to Solve
    - Click Save
    Reply With QuoteRegister To Reply
  11. 10-31-2012, 01:54 PM #11
    meilieng
    meilieng is offline
    Registered User
    Join Date
    10-30-2012
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Which formula to use for Resource Incentive Sheet

    Thanks so much! You were the first person that I have encountered on this site, and I must say I give you a 10 out of 10!
    Reply With QuoteRegister To Reply
  12. 10-31-2012, 01:56 PM #12
    djapigo
    djapigo is offline
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Which formula to use for Resource Incentive Sheet

    No problem... thank you for the rep!
    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)

Tags for this Thread

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