+ Reply to Thread
Results 1 to 7 of 7

summing in excel using sumifs help required

  1. #1
    Registered User
    Join Date
    12-25-2015
    Location
    Pakistan
    MS-Off Ver
    2010
    Posts
    3

    Lightbulb summing in excel using sumifs help required

    Hello all,
    kindly help
    i am making result card of students in ms excel.
    i need to add marks obtained and total marks
    but
    there is a restriction
    there are total 7 subjects with different total marks e.g.
    English (100), History (100), Mathematics (85), Chemistry (85), Physics (75), Computer (75) and Biology (85).

    Rest of subjects are compulsory for students except Biology and Computer
    Those who attempt computer will not be attempting Biology and vice versa

    so i need a formula to get total marks sum

    example what i want
    if ( bio == 0 )
    {
    sum English+History+Mathematics+Chemistry+Physics+Computer
    }
    %answer is 520 %

    else if (comp == 0)
    {
    sum English+History+Mathematics+Chemistry+Physics+Biology
    }
    %answer is 530 %

    %In case some is absent from computer or biology paper we need to add total of their respective subjects so %

    else if ( Comp == 'A' )
    {
    sum English+History+Mathematics+Chemistry+Physics+Computer
    }


    else if (Bio == 'A')
    {
    sum English+History+Mathematics+Chemistry+Physics+Biology
    }

    waiting for a favourable response

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,854

    Re: summing in excel using sumifs help required

    so, assuming your student's names are in column A and the rest of the subjects are in columns B through H as follows ... English (B1), History (C1), Mathematics (D1), Chemistry (E1), Physics (F1), Computer (G1) and Biology (H1) and the subjects are weighted as you've noted above, maybe this will work for you...
    =IF(H2="",SUM(B2:G2)/520,SUM(B2:F2,H2)/530)

    EDIT: pointed out I reversed null and not null.
    Last edited by Sam Capricci; 12-25-2015 at 11:19 AM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,854

    Re: summing in excel using sumifs help required

    This may be an alternative since I can't tell what relevance you are putting on the values in the parens Mathematics (85) for example, I'm assuming you are weighting them.

    =IF(H2="",(SUM(B2,C2,D2*0.85,E2*0.85,F2*0.75,G2*0.75)/520),SUM(B2,C2,D2*0.85,E2*0.85,F2*0.75,H2*0.85)/530)
    Last edited by Sam Capricci; 12-25-2015 at 11:20 AM. Reason: Formula error.

  4. #4
    Registered User
    Join Date
    12-25-2015
    Location
    Pakistan
    MS-Off Ver
    2010
    Posts
    3

    Re: summing in excel using sumifs help required

    thanks but this is not what i want.
    i want only total marks of all subject
    i am attaching a jpg image so that you can understand it easily
    it is a rough excel sheet
    i have also entered a formula to sum all total marks
    it works for bio but doesnt works for computer marks
    hope its now easy to understand
    Attached Images Attached Images

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: summing in excel using sumifs help required

    You need to clarify what you mean by Bio = 0 and Bio ="A" and the same for Comp. If there is an "A" the cell for Bio or Comp cannot hold a value at the same time without being a text cell.
    I think a workbook with realistic entries and results is necessary.
    Welcome to the forum.

    It will make it easier to help you if you include an Excel workbook. (not a picture of a workbook please)

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Registered User
    Join Date
    12-25-2015
    Location
    Pakistan
    MS-Off Ver
    2010
    Posts
    3

    Re: summing in excel using sumifs help required

    formula is working now but when i drag the formula down for more students it changes the sum rows. hope u understand now
    Attached Files Attached Files

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: summing in excel using sumifs help required

    Change the references from relative to absolute. I didn't check the formula for accuracy just the references so that you can fill down without the references changing:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by newdoverman; 12-25-2015 at 03:42 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Summing the number of sales by week, by multiple campaign codes. HELP ON SUMIFS!
    By Adam_S in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-18-2014, 11:01 AM
  2. [SOLVED] SUMIFS - sumif required but dependent on entry in 2 cells
    By karimel_romeo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-21-2014, 01:51 PM
  3. sumifs with a criteria summing
    By watkec in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-13-2014, 10:23 PM
  4. [SOLVED] sumifs function not summing between two dates
    By PhoenixFaery in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2013, 04:57 AM
  5. SUMMING SUMIFS's
    By HangMan in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-17-2013, 11:52 AM
  6. Help Required with Sumif, Sumifs function
    By shantanuk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-03-2012, 08:54 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1