Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Sum with Criteria (2K3)

    I have a spreadsheet in which I would like to sum numbers in a range of cells based on criteria in a cell outside of that range. I have attached a copy of the spreadsheet for reference.

    Example: In the cell AH10 labeled with "S", I would like a total of all numbers in the range c10:ag10 IF the cell under that number has either an "S" or "A".

    Example: In the cell AI10 labeled with "U", I would like a total of all numbers in the range c10:ag10 IF the cell under that number has an "U".

    Example: In the cell AJ10 labeled with "V", I would like a total of all numbers in the range c10:ag10 IF the cell under that number has an "V".
    Easy Access

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum with Criteria (2K3)

    Try these formula:

    <pre>=SUMPRODUCT(((C11:AG11="S")+(C11:AG11="A"))*C 10:AG10)

    =SUMPRODUCT((--(C11:AG11="U"))*C10:AG10)

    =SUMPRODUCT((--(C11:AG11="V"))*C10:AG10)
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sum with Criteria (2K3)

    Thank you so much for your quick response. It was exactly what I needed.
    Easy Access

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum with Criteria (2K3)

    just wanted to add my thanks.

    Managed to get a sum and a count working checking against 3 columns.

    Big Help

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum with Criteria (2K3)

    Congratulations. I am always happy when I don't just help someone get an answer, but learn how to get the answer.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •