Having problems with finding the correct excel formula for column

[aussiemum32]

Excel Formula Capture.PNG

My problem is that i have four different groups in a column and i have to find two totals.
1 Players
2 Accompanying Person
3 Coach
4 Medical
The first total was for players only which I used the formula =COUNTIF(C3:C35,"Player") which gave me the correct total.
The second total i have to find is for the three remaining groups and I have spent all day trying to get the correct formula to add groups 2, 3, and 4 together for a grand total. Can anyone please help me find a solution, its for my tafe course, and although I am learning about formulas I am at a loss as to what I should do

[aussiemum32]

oh and the cell range for this column is C3:C35 so as to make visulation of where each column should be abit easier for anyone offering their help. Thanks bye

[martindwilson]

count the total number using counta then subtract the countif from it

[FDibbins]

of try countifs()?

[oeldere]

Or sumproduct if it has to be working in the excel 2003 and below versions, also.

[FDibbins]

good point, oeldere, but they say they use 2010, hence the countifs

[MarvinP]

Hi,
See the attached where you problem is solved using a pivot table.

[martindwilson]

all op has to do is give 2 totals 1 for players and the other for the rest so the answer is simply total-players no need for countifs/pivots/sumproduct

[MarvinP]

Hi Martin,

Using Pivot Tables it is about 1 second more time to drag the Amt to the values area and sum vs count. I also gave the OP a way to filter the Travel or Not filter along with clicking on the Row Labels filter and removing the Player to get the total of the other 3 types of travelers.

I'm always worried that the OP will come back and ask the secondary question about how to sum travel amounts etc. If more people would learn about Pivot Tables these problems would be easier.

[martindwilson]

yep but it looks like some sort of homework/assignment.i think they would have said "using a pivot table do x"

[aussiemum32]

Excel Formula Capture.PNG

This is the whole table, as you can see in the status column I need to get a total of the cell range (C3:35) for the three groups
(Medical Staff(C23:C25)+Coach(C26:C27)+Accompanying Person(C28:C35)) and add the total of these three criteria groups together into cell C38. I don't know how to make the formla work properly or how to put it together.

I already have the total for "Players" from cell range (C3:C35) using the formula =COUNTIF(C3:C35,"Player") and added the total to cell C37. Going from one criteria to three is what I am having problems with. Any help would be a gift. I hope someone can give me some pointers. I understand how formulas work, but I am still learning, it's part of my Certificate III tafe course, so any help will put me one step closer to finishing my assessment, this question is based on a fictitious sports club taking a trip, along with coaches, medical staff, and a +1 traveller shown by "Accompanying Person" label. Hope that's enough information to let you know what it is I am looking for. Thanks for all your advice so far, it all helps me understand this formula thing a bit more.
PS I added a new image of cells that includes all the cell numbers for referencing.

[MarvinP]

Hi aussiemum32,

Open the file I attached above and play with it a little. Click on the dropdowns and see how it works. It can filter, sort and add things together. You will need to make a Pivot Table our of your data. It will be new to you but read these for help.
http://www.addictivetips.com/windows...-pivot-tables/
http://chandoo.org/wp/2009/08/19/exc...bles-tutorial/
You can even watch videos of this at:
http://www.bing.com/videos/search?q=...ables+tutorial or
http://www.bing.com/videos/search?q=...ables+tutorial

[martindwilson]

lots of ways to skin a cat here everything above will do what you want, do you need seperate totals for the other criteria? you can just countif on each one and ad the formulas together or use countifs, but you know there are 33 people so 33-players is your total if you really need to count the entries use
counta(c3:c35)-countif(c3:35,"players") ie 33-20=13 or just counta(c3:c35)-c37

[aussiemum32]

solution =COUNTA(C23:C25, C26:C27, C28:C35) thank you for all your help even though I didn't use your solution it gave me the next step to figure it out for myself so thank you for that, and making it possible to figure it out for myself. Thank you everyone cause I have spent better part of a day and half figuring it out. damn formulas, too many to remember.