Thread: Using SUMPRODUCT between worksheets matching but cell content

1. 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.

Tommy

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. 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
•