Results 1 to 6 of 6

Thread: Help in Sumproduct and reference formulas

  1. #1

    Help in Sumproduct and reference formulas



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I have a simple problem with excel. I know that it is possible but I just don't know how.
    Could you please help?

    It's mainly a referencing problem.
    Please see attached.

    Thanks,
    PeterSample.xls

  2. #2
    Perhaps something like this?

    In K3 and copy down and across.

    =SUMPRODUCT(($A$2:$C$2=$J3)*($A$3:$C$11)*(E$3:E$11))
    Regards

    Fotis.
    . Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

    . -Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

    .--Don't attach a screenshot--Just attach your Excel file!

    .--KISS(Keep it simple Stupid)

  3. #3
    Quote Originally Posted by Fotis1991 View Post
    Perhaps something like this?

    In K3 and copy down and across.

    =SUMPRODUCT(($A$2:$C$2=$J3)*($A$3:$C$11)*(E$3:E$11))
    Thank you so much. It works.
    At first I was in doubt because the result is not the same with my sample. It turns out that my sample is wrong since the header (column numbers) were included in the sumproduct function.

    Thank you so much.

    Great help!

  4. #4
    Or

    =SUMPRODUCT(INDEX($A$3:$C$11,0,MATCH($J3,$A$2:$C$2,0)),INDEX($E$3:$H$11,0,MATCH(K$6,$E$2:$H$2,0)))

  5. #5
    You are welcome.

    Thanks for the feed back.

    @ Bob.

    Now i have 5 posts! i'll do(provide link for cross posting) alone!
    Regards

    Fotis.
    . Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.

    . -Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.

    .--Don't attach a screenshot--Just attach your Excel file!

    .--KISS(Keep it simple Stupid)

  6. #6
    Quote Originally Posted by Fotis1991 View Post
    Now i have 5 posts! i'll do(provide link for cross posting) alone!
    So you just ditch me

Posting Permissions

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