## Ms Excel Question

5 pts.
Tags:
Microsoft Excel
Microsoft Excel formulas
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...!!!

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

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

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