Adding a range with three variables

[Saul]

I have a spreadsheet that has site, job title and FTE. I want to add the FTE
by site and job title. I use for two variables the sumif function but can not
get it to work with three. For example,

Site Job Title FTE
Beaverton NP 1.0
Beaverton RN 0.8
Hillsboro NP 0.8
Hillsboro RN 1.0
etc.

This list is longer that this but I need to add the total of FTE for a
person who is a NP and works in Beaverton. Can someone help. Thanks.

I have Excel 2003.

 

[Max]

need to add the total of FTE for a person

who is a NP and works in Beaverton

Try Sumproduct for multi-criteria, eg:
=sumproduct((a2:a100="Beaverton")*(b2:b100="NP"),c2:c100)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---

 

[Dave Peterson]

You may want to learn about pivottables.

You could select the range (with a single row of headers in row 1)
Select A1:C5
Data|pivottable and pivot chart report
Follow the wizard until you get to step 3
Click the layout button
Drag the site header to the row field
drag the job title to the column field
drag the FTE header to the data field.
If it doesn't say "sum of", just right click on it and choose sum.

And finish up the wizard.

You'll end up with a summary report like:

Sum of FTE Job Title
Site NP RN Grand Total
Beaverton 1 0.8 1.8
Hillsboro 0.8 1 1.8
Grand Total 1.8 1.8 3.6

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Saul]

Thanks Max. It did work.
--
Thank you.

Try Sumproduct for multi-criteria, eg:
=sumproduct((a2:a100="Beaverton")*(b2:b100="NP"),c2:c100)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik

 

[Max]

Welcome, Saul. Do try out Dave's pivot response as well. Always good to know
of the different options available.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,200, Files: 354, Subscribers: 53
xdemechanik