Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Sumproduct (I think)

    Row 1, columns A-CC in worksheet 2 are various names. Row 4, columns A-CC in worksheet are one-word descriptions of the names. Row 473, columns A-CC contain formulas that return the dates of interviews with the persons named in row 1, from data in another section of worksheet 2. The formula returns a blank (i. e., "") if no interview was done.

    What I would like to do is where a name that appears in row 1 on worksheet 2 is listed in column A on worksheet, and a certain description is present in row 4, say, a "P", look to worksheet 2 and insert in column B on worksheet 1 the date, if any, of the interview of that person that is in row 473 on worksheet 2, but only when the description is say a "P".

    Any ideas?

    Thanks in advance.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about something like the array (confirm with ctrl-shift-enter):
    =INDEX(Worksheet2!A473:CC473,MIN(IF((Worksheet2!A1 :CC1="Tom")*(Worksheet2!A4:CC4="P"),COLUMN(Workshe et2!A473:CC473))))

    It will find the value in the first column of row 473 for "Tom" in row1 with "P" in row 4. You could also find the average date, min, max, etc if there are more than 1 date meeting the criteria...

    [SumProduct will not work since your formula may be null and thus will lead to #Value errors when used in multiplication. If you replaced the nulls with zeroes and knew there was only 1 value in row 473 meeting the criteria (or 1 date and the rest zero), then sumproduct could work:
    =SUMPRODUCT((Worksheet2!A1:CC1="Tom")*(Worksheet2! A4:CC4="P")*Worksheet2!A473:CC473)

    If using XL2007 or greater you can use SUMIFS even if 473 has null strings (so is better than sumproduct in that regard):
    =SUMIFS(Worksheet2!A473:CC473,Worksheet2!A1:CC1,"T om",Worksheet2!A4:CC4,"P")

    But if there are more than 1 matching dates the dates will all be added in sumifs and sumproduct so may not be what you want exactly...]

    Steve
    PS. This doesn't really seem that much different than your question at http://windowssecrets.com/forums/sho...roduct-I-think

    Steve
    Last edited by sdckapr; 2011-05-26 at 19:28. Reason: Added PS and references to Worksheet2

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Steve, I'll give it a try. You are correct, the two questions are similar, but I thought as opposed to summing values I was looking for a particular date, this was a different request.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FYI SUMPRODUCT should work if you use:
    =SUMPRODUCT((Worksheet2!A1:CC1="Tom")*(Worksheet2! A4:CC4="P"),Worksheet2!A473:CC473)

    Alternatively:
    =LOOKUP(2,1/((Worksheet2!A1:CC1="Tom")*(Worksheet2!A4:CC4="P") ),Worksheet2!A473:CC473)
    will return the last item in row 473 that matches both criteria. No array-entry required.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. The Following User Says Thank You to rory For This Useful Post:

    sdckapr (2011-05-27)

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    FYI SUMPRODUCT should work if you use:
    =SUMPRODUCT((Worksheet2!A1:CC1="Tom")*(Worksheet2! A4:CC4="P"),Worksheet2!A473:CC473)
    Thanks, Rory.

    I hadn't realized the subtle difference between SUMPRODUCT(A*B*C) and SUMPRODUCT(A*B,C) when text was involved...

    Steve

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    There are situations where you can't use that and have to use the multiplication syntax all the way (eg multicolumn values range) but as a general rule I prefer the comma syntax for safety.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Rory

Posting Permissions

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