+ Reply to Thread
Results 1 to 4 of 4

Need sum of resources with multiple matching criteria lines

  1. #1
    Registered User
    Join Date
    09-20-2016
    Location
    STL
    MS-Off Ver
    2010
    Posts
    7

    Need sum of resources with multiple matching criteria lines

    Here's what I have - multiple resources billing to the same project, with a line for each time entry by date. Client wants to know the number of onshore/offshore resources billing to each project. I made an =or column D to show offshore v onshore. I need an output that shows me X amount of onshore resources and X amount of offshore resources by project # in column A. Have tried sumifs but am getting bad results. See below for example. Thanks!

    Project Number Expenditure Owning Practice Incurred by Person Name OFFSHORE
    52201 Commerce US Consulting Smith, Sam FALSE
    52201 Commerce US Consulting Smith, Sam FALSE
    52201 Commerce US Consulting Smith, Sam FALSE
    52201 Commerce US Consulting Smith, Sam FALSE
    52201 Commerce US Consulting Smith, Sam FALSE
    52201 Commerce US Consulting Smith, Sam FALSE
    52201 Nagpur MSO Consulting Deshpa, Himanshu TRUE
    52201 Nagpur MSO Consulting Deshpa, Himanshu TRUE
    52201 Nagpur MSO Consulting Deshpa, Himanshu TRUE
    52201 Nagpur MSO Consulting Deshpa, Himanshu TRUE
    52201 Nagpur MSO Consulting Deshpa, Himanshu TRUE
    52201 Nagpur MSO Consulting Muli, Suri TRUE
    52201 Nagpur MSO Consulting Muli, Suri TRUE
    52975 Chennai Consulting Uday, Sayam TRUE
    52975 Chennai Consulting Uday, Sayam TRUE
    52975 Chennai Consulting Uday, Sayam TRUE
    52975 Chennai Consulting Uday, Sayam TRUE
    52975 Chennai Consulting Uday, Sayam TRUE
    52975 Chennai Consulting Raman, Parthi TRUE
    52975 Chennai Consulting Raman, Parthi TRUE
    52975 Chennai Consulting Raman, Parthi TRUE
    52975 Chennai Consulting Raman, Parthi TRUE
    52975 Chennai Consulting Raman, Parthi TRUE
    52975 Chennai Consulting Raman, Parthi TRUE
    52975 Chennai Consulting Raman, Parthi TRUE
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Need sum of resources with multiple matching criteria lines

    Hi rude,

    Perhaps a Pivot Table does what you need? See the attached:
    PT for rude314.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    09-20-2016
    Location
    STL
    MS-Off Ver
    2010
    Posts
    7

    Re: Need sum of resources with multiple matching criteria lines

    Hi Marvin - pivot table doesn't work because it's telling me Sam has 6 time entries - Here's how I want it to read for 52201:

    Project Number Onshore Offshore
    52201 1 2


    Because even though they've entered time on multiple days, all I'm interested in is how many people on the project are onshore v. offshore. On 52201, there are 3 resources...2 are offshore, 1 is onshore.

    Any way to make that happen?

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Need sum of resources with multiple matching criteria lines

    Hey rude,

    Perhaps you want to show the number of people onshore vs offshore on each day. True is onshore and False is offshore. But you have more than 3 people in your example file. See if the attached isn't closer to the answer you want. I think there must be a date column missing and you could filter by any date in the log. Make sense?
    PT for rude314 No 2.xlsx

+ 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. Replies: 5
    Last Post: 04-23-2020, 04:21 PM
  2. Closest match with multiple criteria (3 criteria) Not matching perfectly!
    By JulianS96 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-04-2019, 10:50 AM
  3. Match multiple criteria in different lines
    By AFITT in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-03-2019, 12:54 PM
  4. [SOLVED] Sum Multiple Lines based on Multiple Criteria
    By Netsirkf in forum Excel General
    Replies: 5
    Last Post: 10-29-2018, 05:49 AM
  5. Count/Find X on multiple lines w/ multiple criteria
    By artistapart in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-28-2016, 12:55 PM
  6. [SOLVED] Find matching lines based on multiple criteria
    By Jonathan68 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-13-2014, 06:04 PM
  7. Return multiple matching lines using VLOOKUP?
    By Nick Robinson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-26-2011, 02:39 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