Excel sum problem

[wesleyw]

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

[Norphy]

sumif?

From Excel help..

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.

[CyberNerd]

awk ?

awk '{print $2 $3}'  file.csv  | grep -i 'french' | grep -i 'c' | wc -l

[NetworkGeezer]

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

[Gatt]

try the COUNTIFS() function

=COUNTIFS(Range1,criteria1,Range2,criteria2)

eg:

=COUNTIFS($b3:$b58,"French",$c3:$c58,"A")

[AdrianSpencer]

Why not use a Pivot Table? They are just a few mouse clicks.
Assuming your table looks something like this...

Name Subject Grade
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
Column = Grade
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

[kmount]

Hey Adrian,

Welcome to Edugeek.

Looking at the last time this thread was replied to (3 years ago) I'd think it was resolved by now

[AdrianSpencer]

Ooops. I'll check the dates in future.
"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

[kmount]

hehe, welcome nonetheless, it's always good to see people keen to help