Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2013
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Using SUMPRODUCT between worksheets matching but cell content

    Hi All

    I am currently using the following formula: =SUMPRODUCT(--(Summary!$3:$3>=DATE(YEAR(Q$1),MONTH(Q$1),1)),--(Summary!$3:$3<=DATE(YEAR(Q$1),MONTH(Q$1),DAY(DATE (YEAR(Q$1),MONTH(Q$1)+1,0)))),Summary!53:53)

    The first 2 parts of the formula define the date range that i am looking up and the third part points directly to the line i am looking for in the other worksheet. What i am trying to do is make the third part dynamic where rather than pointing directly to the line i can use match to identify what line in the other worksheet i should look to find the current value.

    For example in the worksheet where the formula is i have "Total X" in cell A2. I can return the row number of "Total X" from the other worksheet using =MATCH(A2,Summary!$A:$A,0). Imagine this returns line 53.

    What i am trying to do is combine the 2 formula's so i no longer have to point directly to the line in the other worksheet.

    Thanks in advance for the advice.

    Tommy

  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:
    Code:
    =SUMPRODUCT(--(Summary!3:3>=DATE(YEAR(Q$1),MONTH(Q$1),1)),--(Summary!3:3<=DATE(YEAR(Q$1),MONTH(Q$1),DAY(DATE(YEAR(Q$1),MONTH(Q$1)+1,0)))),INDIRECT("Summary!"&MATCH(A2,Summary!$A:$A,0)&":"&MATCH(A2,Summary!$A:$A,0)))
    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    Littlestonedrose (2013-09-26)

  4. #3
    New Lounger
    Join Date
    Sep 2013
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Steve cheers for that your suggestion worked perfectly.

    Really much appreciated.

    Tommy

Posting Permissions

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