Ms Excel Question

5 pts.
Tags:
Excel formulas
Microsoft Excel
i have entered different students name in column A, Paper name in Column B & their marks in column C. like: john english 90 magi maths 15 john stats 99 john history 35 & i've applied this formula: =if(A2:A66000="John",sum(C2:C66000)) to get the total marks of John in all papers,but it doesnt work. plz help...!!!
ASKED: July 4, 2008  8:51 AM
UPDATED: February 25, 2009  9:01 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Funny you should ask. I had to figure it out today myself.

I would use the following formula for your issue:

=SUMPRODUCT((A2:A66000=”John”)*((C2:C66000)*1))

However, I wonder if you want the average rather than the sum. If so, use this formula:

=SUMPRODUCT((A1:A66000=”John”)*((C1:C66000)*1))/SUMPRODUCT((A1:A66000=”John”)*1)

The following is what I did today which used named ranges (I find it easier to read).

I created named ranges. For example: Tax Type is C6:C39 and EnvTotal is D6:D39. I then Multiplied the sum (SUMPRODUCT) by RegularTax which is E2 to get the total tax for the items that were taxed at the regular tax value (“R”).

=SUMPRODUCT((TaxType=”R”)*(EnvTotal*1))*RegularTax

I hope this helps.

Tammy

the answer to your query is really simple:
1 name sub marks
2 john english 90
3 maggi maths 15
4 John stats 99
5 john history 35

total marks of john:
formula: =sumif(A2:A5,”john”,C2:C5)

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Ledlincoln
    Here's a simpler solution that doesn't require hard-coding students' names. Use the SUBTOTAL() function for your formula(s), and click the AutoFilter button. SUBTOTAL() will operate on only the rows that are visible when filtered. Formulas: =SUBTOTAL(9,C2:C25) will display the total =SUBTOTAL(1,C2:C25) will display the average The first parameter can supply other things such as minimum, maximum, count, etc. Check Excel Help for details.
    1,620 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following