Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    On Worksheet1, Rows Column A, Rows 1-2000, listing of all possible locations. On Sheet2, Column A, Rows 1 through 350, a listing of all contract numbers that had activity during a given month. On Sheet2, Column B, Rows 1 through 350, a listing of all locations used by the respective contracts during the month.

    On Sheet1, column Z, rows 1-2000 I would like a formula that would return the data from the corresponding cell in Sheet2, where the names of the locations are the same, and the contract number is equal to "123456". I would prefer a function that is not a SUMPRODUCT, as, with 2000 rows, the recalc can take a little time.

    Any ideas?

    Thanks in advance.

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by jlkirk View Post
    On Worksheet1, Rows Column A, Rows 1-2000, listing of all possible locations. On Sheet2, Column A, Rows 1 through 350, a listing of all contract numbers that had activity during a given month. On Sheet2, Column B, Rows 1 through 350, a listing of all locations used by the respective contracts during the month.

    On Sheet1, column Z, rows 1-2000 I would like a formula that would return the data from the corresponding cell in Sheet2, where the names of the locations are the same, and the contract number is equal to "123456". I would prefer a function that is not a SUMPRODUCT, as, with 2000 rows, the recalc can take a little time.

    Any ideas?

    Thanks in advance.
    Hi jlkirk,

    SUMIF can only handle one variable, but you've got two. If you're using Excel 2007, you could use the SUMIFS function.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by macropod View Post
    Hi jlkirk,

    SUMIF can only handle one variable, but you've got two. If you're using Excel 2007, you could use the SUMIFS function.
    Thanks. in fact I am using 07. Any clues?

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by jlkirk View Post
    Thanks. in fact I am using 07. Any clues?
    Hi jlkirk,

    Have you checked out the SUMIFS function? The Excel '07 help file describes its use.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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