Excel Formulas
 

I have created a spreadsheet with a number of columns.
A = Names
B = Locations
C = Projects
D = Attendance in hours per person/per week
E = Attendance in hours per person/per week
F = Attendance in hours per person/per week
G = Attendance in hours per person/per week.

So I enter the different people column A,
The locations that the people live in (column B), against their names
The hours they spend at the project each week for the month.

However in Column D I have a number of different codes: Ins/7/AA/FTA.

What I need to be able to do is sort Column B (location), then sort by
Column C (Project), then get the total values for the rest of the columns.
eg. how many Ins, how many AA etc.

I hope this makes sense. Thank you

Excel Formulas
 

ok, assuming the column names are in A1 thru G1 and you have lets say, 30
rows of names, project info. etc

In D32 use "=COUNTIF(D2:D31,XXXXX) where XXXX is the project code you are
looking for.

In D33, use the same formula changing the next project code. Make sure to
confirm the range of cabs , "D2:D31" is the same, because if you copy it down
(Ctrl D), the range will automatically shift one row down to "D3:D32".

Repeat this going down one cell for each project code you have.



"Cutie" wrote:

I have created a spreadsheet with a number of columns.
A = Names
B = Locations
C = Projects
D = Attendance in hours per person/per week
E = Attendance in hours per person/per week
F = Attendance in hours per person/per week
G = Attendance in hours per person/per week.

So I enter the different people column A,
The locations that the people live in (column B), against their names
The hours they spend at the project each week for the month.

However in Column D I have a number of different codes: Ins/7/AA/FTA.

What I need to be able to do is sort Column B (location), then sort by
Column C (Project), then get the total values for the rest of the columns.
eg. how many Ins, how many AA etc.

I hope this makes sense. Thank you

Excel Formulas
 

Did you maybe mean the in columns D thru G you have the different codes?
Column D is code 7
Column E is code Ins
Etc????

If so, enter your data, sort how ever you want, then you can: (again
assuming you have 30 rows of data)

in D32 use =counta(D2:D31)
in E32 use =counta(E2:E31)
etc.

You may want to move them down to row 34, and in row 33, put something like
"Total for Code 7", "Total for Code Ins". etc

"Cutie" wrote:

I have created a spreadsheet with a number of columns.
A = Names
B = Locations
C = Projects
D = Attendance in hours per person/per week
E = Attendance in hours per person/per week
F = Attendance in hours per person/per week
G = Attendance in hours per person/per week.

So I enter the different people column A,
The locations that the people live in (column B), against their names
The hours they spend at the project each week for the month.

However in Column D I have a number of different codes: Ins/7/AA/FTA.

What I need to be able to do is sort Column B (location), then sort by
Column C (Project), then get the total values for the rest of the columns.
eg. how many Ins, how many AA etc.

I hope this makes sense. Thank you

Excel Formulas
 

HI, sorry I should have said, what you have explained below are in rows and
not the columns:

B C D E
F G Barnet Oakwood Park Ins Ins 7 AA
Barnet Oakwood Park Ins Ins 2 Ins
Camden Kelley House Ins Ins FTA AA

What I would like to do is sort by column B, so I get all the "Barnet".
Once I have that data, I would like to sort by column C. Then get the total
number of "Ins", "AA" etc.

Hope that makes more sense. I wasnt sure if this was best done in a Pivot
Table.

Thank You.
"JasonP CCTM LV" wrote:

ok, assuming the column names are in A1 thru G1 and you have lets say, 30
rows of names, project info. etc

In D32 use "=COUNTIF(D2:D31,XXXXX) where XXXX is the project code you are
looking for.

In D33, use the same formula changing the next project code. Make sure to
confirm the range of cabs , "D2:D31" is the same, because if you copy it down
(Ctrl D), the range will automatically shift one row down to "D3:D32".

Repeat this going down one cell for each project code you have.



"Cutie" wrote:

I have created a spreadsheet with a number of columns.
A = Names
B = Locations
C = Projects
D = Attendance in hours per person/per week
E = Attendance in hours per person/per week
F = Attendance in hours per person/per week
G = Attendance in hours per person/per week.

So I enter the different people column A,
The locations that the people live in (column B), against their names
The hours they spend at the project each week for the month.

However in Column D I have a number of different codes: Ins/7/AA/FTA.

What I need to be able to do is sort Column B (location), then sort by
Column C (Project), then get the total values for the rest of the columns.
eg. how many Ins, how many AA etc.

I hope this makes sense. Thank you

Excel Formulas
 

HI
Columns D thru G have a mixture of codes eg: 7, Ins, AA. This is because if
someone attends the project I record the hours, hence 7. If they do not
attend FTA, etc.

"JasonP CCTM LV" wrote:

Did you maybe mean the in columns D thru G you have the different codes?
Column D is code 7
Column E is code Ins
Etc????

If so, enter your data, sort how ever you want, then you can: (again
assuming you have 30 rows of data)

in D32 use =counta(D2:D31)
in E32 use =counta(E2:E31)
etc.

You may want to move them down to row 34, and in row 33, put something like
"Total for Code 7", "Total for Code Ins". etc

"Cutie" wrote:

I have created a spreadsheet with a number of columns.
A = Names
B = Locations
C = Projects
D = Attendance in hours per person/per week
E = Attendance in hours per person/per week
F = Attendance in hours per person/per week
G = Attendance in hours per person/per week.

So I enter the different people column A,
The locations that the people live in (column B), against their names
The hours they spend at the project each week for the month.

However in Column D I have a number of different codes: Ins/7/AA/FTA.

What I need to be able to do is sort Column B (location), then sort by
Column C (Project), then get the total values for the rest of the columns.
eg. how many Ins, how many AA etc.

I hope this makes sense. Thank you

Excel Formulas
 

So something like:

Name Location Project Hours Hours
Al Smith Brent Door Ins 7
Al Smith Brent Door Ins 7
Al Smith Brent Floor FTA FTA
Al Smith Brent Hall Ins FTA
Al Smith Coop Door 7 7
Al Smith Coop Floor Ins Ins
Al Smith Coop Hall FTA Ins

Hours Worked 7 21
Code FTA 2 2
Code Ins 4 2


With "Name" in cell A1, enter your data, and do your sort.
Assuming you have 7 rows like the example,
in B10 or C10, put "Hours Worked"
in D10, put =SUM(D2:D8) then copy this across to columns E thru G
in B11 or C11, put "Code FTA"
in D11, put =COUNTIF(D2:D8,"fta") then copy this across to columns E thru G
in B12 or C12, put "Code Ins"
in D12, put =COUNTIF(D2:D8,"ins") then copy this across to columns E thru G
Continue down for whatever codes you have and adjusting the ranges (D2:D8)
for how many rows you have.

I hope this is what you are looking for.






"Cutie" wrote:

HI
Columns D thru G have a mixture of codes eg: 7, Ins, AA. This is because if
someone attends the project I record the hours, hence 7. If they do not
attend FTA, etc.

"JasonP CCTM LV" wrote:

Did you maybe mean the in columns D thru G you have the different codes?
Column D is code 7
Column E is code Ins
Etc????

If so, enter your data, sort how ever you want, then you can: (again
assuming you have 30 rows of data)

in D32 use =counta(D2:D31)
in E32 use =counta(E2:E31)
etc.

You may want to move them down to row 34, and in row 33, put something like
"Total for Code 7", "Total for Code Ins". etc

"Cutie" wrote:

I have created a spreadsheet with a number of columns.
A = Names
B = Locations
C = Projects
D = Attendance in hours per person/per week
E = Attendance in hours per person/per week
F = Attendance in hours per person/per week
G = Attendance in hours per person/per week.

So I enter the different people column A,
The locations that the people live in (column B), against their names
The hours they spend at the project each week for the month.

However in Column D I have a number of different codes: Ins/7/AA/FTA.

What I need to be able to do is sort Column B (location), then sort by
Column C (Project), then get the total values for the rest of the columns.
eg. how many Ins, how many AA etc.

I hope this makes sense. Thank you

Excel Formulas
 

Thank you. This is brilliant. I will go with the formula's you have given me
but it is now raising more questions like. How do I sort the columns to give
me counts of values. eg.

Brent (how many times is the project "door", "Floor", "Hall"), how many
instances of Ins, 7, FTA.

Thanks again. The mind is working overtime now. I am excited!

"JasonP CCTM LV" wrote:

So something like:

Name Location Project Hours Hours
Al Smith Brent Door Ins 7
Al Smith Brent Door Ins 7
Al Smith Brent Floor FTA FTA
Al Smith Brent Hall Ins FTA
Al Smith Coop Door 7 7
Al Smith Coop Floor Ins Ins
Al Smith Coop Hall FTA Ins

Hours Worked 7 21
Code FTA 2 2
Code Ins 4 2


With "Name" in cell A1, enter your data, and do your sort.
Assuming you have 7 rows like the example,
in B10 or C10, put "Hours Worked"
in D10, put =SUM(D2:D8) then copy this across to columns E thru G
in B11 or C11, put "Code FTA"
in D11, put =COUNTIF(D2:D8,"fta") then copy this across to columns E thru G
in B12 or C12, put "Code Ins"
in D12, put =COUNTIF(D2:D8,"ins") then copy this across to columns E thru G
Continue down for whatever codes you have and adjusting the ranges (D2:D8)
for how many rows you have.

I hope this is what you are looking for.






"Cutie" wrote:

HI
Columns D thru G have a mixture of codes eg: 7, Ins, AA. This is because if
someone attends the project I record the hours, hence 7. If they do not
attend FTA, etc.

"JasonP CCTM LV" wrote:

Did you maybe mean the in columns D thru G you have the different codes?
Column D is code 7
Column E is code Ins
Etc????

If so, enter your data, sort how ever you want, then you can: (again
assuming you have 30 rows of data)

in D32 use =counta(D2:D31)
in E32 use =counta(E2:E31)
etc.

You may want to move them down to row 34, and in row 33, put something like
"Total for Code 7", "Total for Code Ins". etc

"Cutie" wrote:

I have created a spreadsheet with a number of columns.
A = Names
B = Locations
C = Projects
D = Attendance in hours per person/per week
E = Attendance in hours per person/per week
F = Attendance in hours per person/per week
G = Attendance in hours per person/per week.

So I enter the different people column A,
The locations that the people live in (column B), against their names
The hours they spend at the project each week for the month.

However in Column D I have a number of different codes: Ins/7/AA/FTA.

What I need to be able to do is sort Column B (location), then sort by
Column C (Project), then get the total values for the rest of the columns.
eg. how many Ins, how many AA etc.

I hope this makes sense. Thank you

Excel Formulas
 

The formula for "Hours Worked" gives you the total number of hours worked,
not the number of times 7 shows up.

If you want it to show the number of times 7 shows up, use the formula for
"FTA" and just change the FTA to 7 in the formula.

"Cutie" wrote:

Thank you. This is brilliant. I will go with the formula's you have given me
but it is now raising more questions like. How do I sort the columns to give
me counts of values. eg.

Brent (how many times is the project "door", "Floor", "Hall"), how many
instances of Ins, 7, FTA.

Thanks again. The mind is working overtime now. I am excited!

"JasonP CCTM LV" wrote:

So something like:

Name Location Project Hours Hours
Al Smith Brent Door Ins 7
Al Smith Brent Door Ins 7
Al Smith Brent Floor FTA FTA
Al Smith Brent Hall Ins FTA
Al Smith Coop Door 7 7
Al Smith Coop Floor Ins Ins
Al Smith Coop Hall FTA Ins

Hours Worked 7 21
Code FTA 2 2
Code Ins 4 2


With "Name" in cell A1, enter your data, and do your sort.
Assuming you have 7 rows like the example,
in B10 or C10, put "Hours Worked"
in D10, put =SUM(D2:D8) then copy this across to columns E thru G
in B11 or C11, put "Code FTA"
in D11, put =COUNTIF(D2:D8,"fta") then copy this across to columns E thru G
in B12 or C12, put "Code Ins"
in D12, put =COUNTIF(D2:D8,"ins") then copy this across to columns E thru G
Continue down for whatever codes you have and adjusting the ranges (D2:D8)
for how many rows you have.

I hope this is what you are looking for.






"Cutie" wrote:

HI
Columns D thru G have a mixture of codes eg: 7, Ins, AA. This is because if
someone attends the project I record the hours, hence 7. If they do not
attend FTA, etc.

"JasonP CCTM LV" wrote:

Did you maybe mean the in columns D thru G you have the different codes?
Column D is code 7
Column E is code Ins
Etc????

If so, enter your data, sort how ever you want, then you can: (again
assuming you have 30 rows of data)

in D32 use =counta(D2:D31)
in E32 use =counta(E2:E31)
etc.

You may want to move them down to row 34, and in row 33, put something like
"Total for Code 7", "Total for Code Ins". etc

"Cutie" wrote:

I have created a spreadsheet with a number of columns.
A = Names
B = Locations
C = Projects
D = Attendance in hours per person/per week
E = Attendance in hours per person/per week
F = Attendance in hours per person/per week
G = Attendance in hours per person/per week.

So I enter the different people column A,
The locations that the people live in (column B), against their names
The hours they spend at the project each week for the month.

However in Column D I have a number of different codes: Ins/7/AA/FTA.

What I need to be able to do is sort Column B (location), then sort by
Column C (Project), then get the total values for the rest of the columns.
eg. how many Ins, how many AA etc.

I hope this makes sense. Thank you