Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Sum Range Using 1 Column of Named Range as Criteria

  1. #1
    Join Date
    21st September 2006
    Posts
    312

    Sum Range Using 1 Column of Named Range as Criteria

    Does anyone know how to use SUM Formula a column from within a Named Ranges or Dynamic Named Range?

    For example, if the range name "MyData" refers to the address: A1:G10, how could I sum all the numbers in column G of that range where column A meets certain criteria.

    Eg., Column A holds fruit names:
    Apple
    Orange
    Banana
    Apple

    and column G holds quantities of the particular fruit.

    I'd like to sum column G (quantity) for only those quantities that match "Apple" in column A.

    Brad.
    Last edited by Dave Hawley; December 21st, 2006 at 14:47.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Sum A Column Within A Dynamic Named Range

    Hi,

    =SUMIF(INDEX(MyData,,1),"Apple",INDEX(MyData,,7))

    Multiple Criteria SUM

    HTH
    Last edited by Dave Hawley; December 21st, 2006 at 14:48.

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: Sum A Column Within A Dynamic Named Range

    Why not simply

    =SUMIF(Fruits,"Apple",Numbers)

    Where Fruits is a Named Ranges of G1:G10 and Numbers is another of A1:A10
    Last edited by Dave Hawley; December 21st, 2006 at 14:51.

  4. #4
    Join Date
    21st September 2006
    Posts
    312

    Re: Sum A Column Within A Dynamic Named Range

    Thanks Kris.

    That's exactly what I was looking for.

    Brad.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Concantenate Cells In Column With Named Range
    By masor017 in forum Excel General
    Replies: 7
    Last Post: May 2nd, 2008, 09:43
  2. Defining Named Range, Error Criteria
    By kippy in forum Excel General
    Replies: 4
    Last Post: April 13th, 2007, 05:40
  3. Define range based on named row and column
    By Anders Lund in forum Excel General
    Replies: 3
    Last Post: July 26th, 2006, 14:43
  4. Replies: 2
    Last Post: March 8th, 2005, 23:33
  5. Dynamically Named Criteria Range
    By mhabib in forum Excel General
    Replies: 1
    Last Post: February 27th, 2004, 19:47

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
  •  
porno