Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: Functions that return values

    I am trying to write a function that returns a value that meets certain criteria. I have a worksheet that has about 40 rows and I want to return the number to a different worksheet that meets two criteria. What function would you recommend.

    Ex if value in column A = Tree and value in column B = 1999 then total and show on a different worksheet.

    Hope that is clear enough.

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Re: Functions that return values

    Is this what your after?

    =SUMPRODUCT((A1:A40 = "Tree")*(B1:B40 = 1999)*(C1:C40))

    where A has to equal Tree, B has to equal 1999 and C is a Number
    This sums the numbers when A is True and B is True sum the values in C

    David

  3. #3
    Join Date
    Mar 2004
    Posts
    361
    Thanks that perfect

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Just as a reminder, when you use SUMPRODUCT, you have to specify the rows. You cannot use A:A (as you can in other functions), it has to be A2:A50 or something like that.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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