Results 1 to 8 of 8

Thread: Help on SUMIF calculation

  1. #1

    Lightbulb Help on SUMIF calculation



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

    Hi,

    I'm having a problem on how to use the SUMIF calculation correct.

    My problem is:
    I want to show the top5 biggest deviations between two numbers.

    This is quite easily and can be done by this formula:
    =SUM(LARGE(Inputs!H:H;{1}))

    What i then want to do, is to dinstinguish the deviations according to the actual week.

    If i have a coloumn with weeks, a column with eg. input, one with output, and one with output.

    How can i then use the SUMIF function to summarize the top 5 deviations in the actual week?

    Can anybody help?

    - Sigvaldaon

  2. #2
    Is this what you want?

    =SUMPRODUCT(LARGE(Inputs!H:H,ROW(INDIRECT("1:5"))))

  3. #3
    I want to show the biggest deviation according to what the actual week is. If i for instance are having week 22, but the table contains data from week 1 to week 22, how can i then distinguish the top 5 deviations in week 22 from all the other weeks?

  4. #4
    No idea without seeing how the data is layed out.

  5. #5
    is there any way of sharing the excel template in here?

  6. #6
    Click the 'Go ADvanced' button, and there is a 'Manage ATtachments' button there.

  7. #7

    Example

    Hi heres an example of my sheet.

    In the sheet "Top 5 Deviations" it is showing the biggest deviations for all weeks. What i want is to only show the top 5 deviations from the actual week.
    Attached Files Attached Files

  8. #8
    Use these array formulae:

    A11: =SUM(LARGE(IF(LOOKUP_SHEET!B:B=$B$3,Inputs!E:E),ROW(A1)))

    C11: =INDEX(LOOKUP_SHEET!C:C,MATCH(1,($A11=LOOKUP_SHEET!$A1:$A200)*($B11=LOOKUP_SHEET!$B1:$B200),0))

Posting Permissions

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