# summing in excel using sumifs help required

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

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

%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  Register To Reply

2. ## 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.  Register To Reply

3. ## 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)  Register To Reply

4. ## 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  Register To Reply

5. ## 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  Register To Reply

6. ## 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  Register To Reply

7. ## 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:  `Please Login or Register  to view this content.`  Register To Reply

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