+ Reply to Thread
Results 1 to 5 of 5

Add cells with multiple criteria in Excel 2003

  1. #1
    Registered User
    Join Date
    02-15-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2003
    Posts
    16

    Add cells with multiple criteria in Excel 2003

    Hi All,

    Still working on this damn resource spreadsheet!

    Now I am trying to add a range of cells depending on certain criteria. Please refer to the attached screen shot.

    What i would like is a formula which looks for all 'billable' criteria in a range, then looks for all 'company' criteria in a range and if they are both true, add up the value(s) of an adjoining column.

    In the example attached, i would like the formula to look up the 'billability' column AND the 'csg' column and if the cell in the billability column = 'billable' AND if the cell in the csg column = 'power' add up the corresponding values in W/E 19/02/2010. In this case the answer would be 4.

    An additional variation to the formula would be to check for a combined 'non-billable' AND 'overhead' in the billability column AND if the cell in the csg column = 'power' add up the corresponding values in W/E 19/02/2010.

    Again any assistance would be greatly appreciated. In this instance i am not looking for a response involving a pivot table.

    Cheers.
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Add cells with multiple criteria in Excel 2003

    Hi

    attaching an XL file would help...

  3. #3
    Registered User
    Join Date
    02-15-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Add cells with multiple criteria in Excel 2003

    Hi,
    Attached is a portion of the excel sheet.
    The answer will need to be saved on another sheet.
    This format will be for someone to input their respective resourcing.

    Cheers.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-15-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2003
    Posts
    16

    Add cells with multiple criteria in Excel 2003

    Hi All,

    I had no luck with my first posting so thought i would try one more time.

    Still working on this damn resource spreadsheet!

    Now I am trying to add a range of cells depending on certain criteria. Please refer to the attached excel file as required.

    What i would like is a formula which looks for all 'billable' criteria in a range, then looks for all 'company' criteria in a range and if they are both true, add up the value(s) of an adjoining column.

    In the example attached, i would like the formula to look up the 'billability' column AND the 'csg' column and if the cell in the billability column = 'billable' AND if the cell in the csg column = 'power' add up the corresponding values in W/E 19/02/2010. In this case the answer would be 4.

    An additional variation to the formula would be to check for a combined 'non-billable' AND 'overhead' in the billability column AND if the cell in the csg column = 'power' add up the corresponding values in W/E 19/02/2010.

    Again any assistance would be greatly appreciated. In this instance i am not looking for a response involving a pivot table.

    Cheers.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Add cells with multiple criteria in Excel 2003

    Hi,

    Have a try with ...
    =SUMPRODUCT((E14:E43="Billable")*(F14:F43="Power")*(M14:M43))
    HTH

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1