+ Reply to Thread
Results 1 to 6 of 6

Count Chargeable Resources

  1. #1
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Question Count Chargeable Resources

    Hi,

    I'm conducting a forecast & have a list of positions with estimated percentage or hours.

    I'm looking to "count" prospective resources or individuals charging per week or during specified days - Monday through Sunday's.

    For cumulative estimated hours I used:

    =SUMIF($D$43:$D$84,"<>*Est*",C15:C56)
    -- Finding "Est" as a key abbreviation to find & sum.

    I'm looking to do something similar but I have unique positions & therefore can't use a key word abbreviation.


    I attached a table for example.

    Looking for solution in row 12
    Attached Files Attached Files
    MyCon
    -- Using Latest Version of Excel

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Count Chargeable Resources

    I've rehashed your sheet a bit..to ensure there were only numbers instead of ""

    In D17 I changed the formula to
    =IF(C17="",0,C17)
    dragged across

    In C18 I changed it to
    =IF(C17="",0,C15*C17)
    dragged across

    Since your layout is pretty standard otherwise, you just need to sum every 5th row. Hence,

    In C11
    =SUMPRODUCT((MOD(ROW(C15:C58),5)=MOD(ROW(C15),5))*C15:C58)

    Copied across

    See attached
    Attached Files Attached Files
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Re: Count Chargeable Resources

    Hi Ace_XL,

    Thanks for quickly replying or providing your suggestions.

    Unless, I'm doing something wrong, I'm getting another variation of total cumulative hours, which I already have.

    Perhaps, this will help

    I have 10 positions listed. Out of these - 6 have (forcasted) chargeable hours. Therefore, in row 12 - I'm trying to bring this out.

    With the following to find total cumulative hours & seeking something common in each row, such as "Est" - Short for Estimated, the function works great.
    =SUMIF($D$43:$D$84,"<>*Est*",C15:C56)
    -- Finding "Est" as a key abbreviation to find & sum.

    Now to count individuals or positions who are charging, I don't have that common word or abbreviation to seek & therefore, can't use the same function.

    What's yours or others suggestions to "count" total individuals or positions charging to program?

    Thanks again
    Last edited by mycon73; 08-30-2015 at 09:42 AM.

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Count Chargeable Resources

    There must be a better way of doing this but I haven't been able to find it. I used helper columns H:L and entered this formula in H15 and filled across and down to identify all values that were greater that 0 and enter a 1 for each of them and a 0 if not greater than 0.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In C12 I entered this formula and filled across that would sum all the 1s from every 5th row between H15 and L58
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    This non array formula can also be used in C12 and filled across:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by newdoverman; 08-30-2015 at 10:36 AM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Re: Count Chargeable Resources

    Hi newdoverman,

    Wow!! It works!

    Except, it's additional additional (hidden or off to the side) rows or columns to do this, which I'm not liking.

    I have a fairly large spreadsheet or what I like to called a Big *** Spreadsheet (BASS).

    Anyone,
    Is there any way to do this without adding numerous rows or columns?

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Count Chargeable Resources

    I don't like the helpers either but sometimes they can solve problems. The helper columns can be way off to the right out of the way and hidden if you like.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA resources
    By RS15 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-15-2013, 07:52 AM
  2. Resources
    By carfang in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 01-16-2011, 08:25 AM
  3. No available resources ???
    By Rivers in forum Excel General
    Replies: 3
    Last Post: 07-19-2010, 05:14 PM
  4. macro/formula to count distinct resources and match to location
    By nikoniko in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-08-2009, 12:44 PM
  5. [SOLVED] Available resources
    By Raju in forum Excel General
    Replies: 0
    Last Post: 06-12-2006, 06:05 AM
  6. calculation for bank interest chargeable
    By Tiya in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-01-2006, 01:58 PM
  7. certificate of chargeable gain
    By robinhood in forum Excel General
    Replies: 2
    Last Post: 12-02-2005, 12:30 PM
  8. [SOLVED] Available Resources
    By Katherine in forum Excel General
    Replies: 0
    Last Post: 05-10-2005, 03:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1