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

    Sumproduct I think

    I posted this on Mr Excel, but haven't heard anything.

    On Worksheet 2 I have 100 columns. In row 1 are names, some of which are repeated. In row 4 is a one letter code, either an "L" or an "R". Rows 5 through 5000 are data in each of the cells in each column. In row 5001 is an average of the data in each column. What I would like to do is sum all of the avarages that correspond to the same name, and same code letter into a cell, say A1 on wortksheet 1.

    For example, on worksheet 2, in row 1, column A is the name "John". John also appears in row 1, columns E, F, and G. In the corresponding cells in row 4 are an "R", "L", "R", and "R", respectively. What I would like to do is add all of the averages in row 5001 where the entries in the corresponding cells in rows 1 and 5 are John and "R". In my example, cells A5001, F5001 and G5001 would be sumed at Worksheet 1, cell A1. 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
    =SUMPRODUCT((Worksheet2!A1:CV1="John")*(Worksheet2 !A4:CV4="R")*Worksheet2!A5001:CV5001)

    Steve
    PS: If you have XL2007 or XL2010 you can use the SUMIFS formula as well:
    =SUMIFS(Worksheet2!A5001:CV5001,Worksheet2!A1:CV1, "John",Worksheet2!A4:CV4,"R")
    Last edited by sdckapr; 2011-05-25 at 13:18. Reason: Added PS

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Steve,
    I have done something wrong-it doesn't seem to work for me. Please see attached.
    Thanks.
    Attached Files Attached Files

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Never mind, I found it!

Posting Permissions

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