# Excel sum problem

• 31st August 2006, 12:09 PM
wesleyw
Excel sum problem
I need to workout how many pupils have say taken French (column b) and have attained an a grade (column c)

something like =sum((\$b3:\$b58=condition1)*(\$c3:\$c58=condition2))

but I can't remember the exact way of doing this I believe there is also when using the formula woizard some key to press as well but it's been 12 months and I can't remember what I did last time?

Wes
• 31st August 2006, 12:30 PM
Norphy
Re: Excel sum problem
sumif?

From Excel help..

Quote:

SUMIF

Adds the cells specified by a given criteria.

Syntax

SUMIF(range,criteria,sum_range)

Range is the range of cells you want evaluated.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".

Sum_range are the actual cells to sum.

Remarks

The cells in sum_range are summed only if their corresponding cells in range match the criteria.

If sum_range is omitted, the cells in range are summed.

Microsoft Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF function. To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF function.

• 31st August 2006, 12:32 PM
CyberNerd
Re: Excel sum problem
awk ?

Code:

`awk '{print \$2 \$3}'  file.csv  | grep -i 'french' | grep -i 'c' | wc -l`
• 31st August 2006, 12:43 PM
NetworkGeezer
Re: Excel sum problem
What?! Formulas? Exports? scripts?
Too much like hard work.

Why not just use the automatic filter (Data menu) on the table.

Set filter to match subject French and set the filter on column b to match.
It should tell you the number of records that match on the status line.

@Norphy you should use COUNTIF because I think SUMIF is for numbers.

HTH
• 31st August 2006, 07:30 PM
Gatt
Re: Excel sum problem
try the COUNTIFS() function

Code:

`=COUNTIFS(Range1,criteria1,Range2,criteria2)`
eg:
Code:

`=COUNTIFS(\$b3:\$b58,"French",\$c3:\$c58,"A")`
• 29th August 2009, 11:54 AM
Why not use a Pivot Table? They are just a few mouse clicks.
Assuming your table looks something like this...

Name1 Maths c
Name2 Maths a
Name3 Maths b
Name4 Maths d
Name5 Maths a
Name6 French a*
Name7 French b
Name8 French b
Name9 French c
Name10 French c
Name11 English b
Name12 English a*

If you create a Pivot Table [Data > Pivot Table and Pivot Chart Report... in 2003] or [Insert Tab in 2007].

Row = Subject
Data = Count of Name (I find Admission Number more reliably unique for counting)

You then get... [tried to paste it here but the format went horribly wrong]...what you want.

If you use SIMS you can automate this from SIMS report. I can dig out some instructions for this if you wish.

"One hundred idiots make idiotic plans and carry them out. All but one justly fail. The hundredth idiot, whose plan succeeded through pure luck, is immediately convinced he's a genius." Iain M Banks
• 29th August 2009, 12:03 PM
kmount

Welcome to Edugeek.

Looking at the last time this thread was replied to (3 years ago) I'd think it was resolved by now :)
• 29th August 2009, 12:16 PM
Ooops. I'll check the dates in future.:doh:
"One hundred idiots make idiotic plans and carry them out. All but one justly fail. The hundredth idiot, whose plan succeeded through pure luck, is immediately convinced he's a genius." Iain M Banks
• 29th August 2009, 12:23 PM
kmount
hehe, welcome nonetheless, it's always good to see people keen to help :)