Home Forum Chat Forum Boring Exceltrackworld question

Viewing 10 posts - 1 through 10 (of 10 total)
• Boring Exceltrackworld question
• AndyRT
Subscriber

You could use a pivot table.

That way you can isolate a variable to find trends easily without having to do any Visual Basic.

As long as you have saved the spreadsheet you can go to Insert, Pivot table, do it on a new worksheet and then drag and drop the variables to the table.

it may help to check out Exel help first. Once you get the hang of Pivot tables you will wonder how you ever worked without them.

mattyd
Member

Yep, a second vote for a pivot table.

annebr
Subscriber

=sumif(range,criteria,[sumrange])

mogrim
Member

3rd vote!

Easiest way is:

1. Select all the data you want to use, including the column headers

2. Click on the convert to table button (on the main ribbon)

3. Select insert ribbon, then click on insert pivot table. Select the fields you want to use.

brakes
Member

I’d use sumif because I hate pivot tables.
you’ll need a column of the lecturers names though to refer to – they are the ‘criteria’ in the formula posted by annebr, range is the table you’ve posted above and sum range is the column of numbers.

austin
Member

Thanks, I’ll look into pivot tables. Am I right in thinking that if I used sumif, I’d have to use a different formula for each teacher name?

annebr
Subscriber

yes that’s right you have a row per teacher name and that formula by each name

austin
Member

Please Excel nerds, can you tell me how to get the sum of all values in one column which have the same value in another column? Lecturers have students in different classes and I want to know how many students they have in total. e.g.

Name | Number of Students
Smith | 20
Smith | 25
Jones | 15
Jones | 10
Jones | 7
Brown | 5
Brown | 20

Is it possible to have a single formula in a column which will do this? There are about 380 staff teaching about 2,000 classes.

jfletch
Member

You don’t need a different formula if you go Sumif. You just need to have the correct cell references in each formula which can be achived by careful application of a few \$ signs.

I’d go pivot for either a one off data analysis or if the teachers names change a lot. I’d go sumif if the teachers names are relatively stable but I want the spreadhseet to be a long standing tool.

Pivot tabels are useful for quick analysis but a bit clunky to use all the time as they tend to lose formatting and be a bit funny with links elsewhere.

austin
Member

PIVOT TABLES TO THE RESCUE! Thanks for your help

Viewing 10 posts - 1 through 10 (of 10 total)

The topic ‘Boring Exceltrackworld question’ is closed to new replies.