Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUMPRODUCT As a replacement for Lookup formulas (2000)

    I have fcome across a way of not using Lookup formulas for a table of data using the Sumproduct formula, It seems to work , but the only thing is im not sure why it works and i want to justify it to myself as a method.

    Basically I have a table say with the headings going from B2 across on sheet1

    GROUP , SUBGROUP , VALUE

    I then have data like the following Under the columns headings

    Adoption, 1 to 2 Years Old , 4
    Adoption, 3 to 5 Years old, 5
    Adoption, 7 to 8 Years old , 2
    Care, 1 to 2 Years Old , 4
    Care, 3 To 5 years Old , 2
    Care 7 To 8 Years Old, 3
    Family 1 to 2 Years Old, 6
    Family 3 To 5 Years Old, 4
    Family 7 To 8 Years Old , 3

    Etc etc etc etc

    Now I want to show this data in a table Visually appealing so i set up on a new worksheet (sheet2) the column headings, again starting in B2 going across

    Type, 1 to 2 Years Old, 3 To 5 Yers old, 7 To 8 Years Old

    And under Type
    Adoption
    Care
    Family

    I then use the sum product formula , for example to get the figure for Care , 3 To 5 Years old My formula looks like this =SumProduct((Sheet1!B3:B4 = sheet2!B2)*(Sheet1!C3:C4 = sheet2!C2)*(Sheet1D34)) And that brings me back the right figure ,also if i use dollar signs i can simply drag and copy this formula across the table to bring back all the right values

    Why does this work ?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMPRODUCT As a replacement for Lookup formulas (2000)

    <P ID="edit" class=small>(Edited by WebGenii on 01-Apr-03 09:22. to add hyperlink)</P>http://www.mrexcel.com/wwwboard/messages/8961.html

    http://makeashorterlink.com/?I2EC527F2
    Microsoft MVP - Excel

Posting Permissions

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