How do you do....it? Thread, Excel sum problem in Technical; I need to workout how many pupils have say taken French (column b) and have attained an a grade (column ...
1. ## 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

2. ## Re: Excel sum problem

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.

3. ## Re: Excel sum problem

awk ?

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

4. ## 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

5. ## Re: Excel sum problem

try the COUNTIFS() function

Code:
`=COUNTIFS(Range1,criteria1,Range2,criteria2)`
eg:
Code:
`=COUNTIFS(\$b3:\$b58,"French",\$c3:\$c58,"A")`

6. 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

Welcome to Edugeek.

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

8. ## Thanks to kmount from:

9. 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

10. ## Thanks to AdrianSpencer from:

kmount (29th August 2009)

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

SHARE:

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•