My boss gave me a new spreadsheet to add formulas to (which i'm not that too good add) but learning as some people show me. So here's what I got

I have 4 sheets in a work book sheet3 and sheet4 are pretty much the same type of STAT sheet, so we'll work off of sheet3 and I will try to figure out on sheet4 by myself, from what I learn from sheet3.

In sheet3 i have 5 columns on two rows, with the top row being header of information that it's asking for. The second row I need to enter formula that will get total # from another sheet (sheet1).



From sheet1 i need to know the total of a word. Say for instant add up all the words that has the name Steve. On sheet3 it will give me a total of # that it sees the word Steve in sheet1 in column I

Also I need a total # of entry that I can put at the bottom of sheet1.

If you can help thanks, if you need me to upload and example of what I need, I would be glad to link you to a exsample file.

I am providing a link to the form I need help with

speardsheet - Windows Live

The cell that are highlight in yellow are the ones I need help with

Right - I'll need a bit more info, please:

Using your uploaded test sheet as an example - in the worksheet 'TECH ONLY' what exactly is it that you want to add up - can you tell me the exact column and word based on your workbook!


Bird

it's to only add up how many rows that column has something in it. so if there are 50 rows, and only say 20 has something in that column then the total would be 20.

So you are only looking to count if there is ANYTHING in the row - OK

Type [=COUNTA(] (without [] brackets) and then go to the worksheet you want and select the range then type[)] and enter

COUNTA function will count all the cells and return the number that contain data

Using the example sheet that you uploaded the PRW Only total box would contain the following formula if it were counting all the Sales Person column in the TECH ONLY worksheet:

If you are looking to count a particular word, not ANY data, this will need to be COUNTIF instead and would look like this:

If you were looking for how many times the name Phil were to appear in the First Name column on the TECH ONLY worksheet...

Is that any help?



BTW - in PRW ONLY STATS cell F8 you should use a range operator, rather than individual cells when using =SUM - only when the cells are not next to each other do you need to revert to x+x+x+x

ie. =SUM(B8:E8) is the same as =(B8+C8+D8+E8)

in the second example you don't need SUM because you are using the + symbol which is what =SUM means!

Welcome to the fun world of Excel formulas - just wait till BODMAS comes along to bite you - LOL!

Thank you BIRD. Sorry I got the responce a week ago via email, and then my email crash, and I belong to some many MSOffice Forums that I forgot which forum I was in when I recieved this posting. But I will try out your formula and give you a responce back asap. Once again thanks!

Hey BIRD, there's been another change in the plan. I wish my Supervisor would just make up their minds how they want this done, so that I can be done with it. So here's the problem

They want a formula that will total on which officer has pend in on cell and which officer has techs in another.

example: officer joe has 12 tech and 20 pending
officer john has 20 tech and 10 pending
officer doe has 50 tech and 20 pending

SHEET#1
colm #A will be officer name, colm #B will have the word tech or pend beside the officer row that the data is enter. On SHEET#2 the STATS will refect the total # of tech and a total # of pend for each officer.

No worries!

Explanation

COUNTIFS - multiple conditions version of COUNTIF
Sheet1! - this is the name of the sheet that the range is on - ALWAYS put the ! at the end!!
RANGE1 - the range (eg: A2:A123) that contains the Officers names
"NAME" - Officers name - replace the word NAME, but keep the ""
RANGE2 - the range (eg: B2:B123) that contains the words 'tech' or 'pend'
CONDITION - however you want to word, 'tech' or 'pend'

egThis code, for example would look in Column A for the word 'Bill', then look in column B and check if the word 'Tech' is there - it will return a value only if both conditions are met. You would then have to have the following formula in another cell:
You will need to replicate the code twice for each Officer - one for tech, and one for pend, but the formula is there above, so all you have to do is alter it

Have fun!

Well Bird, i've tried your formula and it seems that it doesn't work. I have another speadsheet to show you the testsheet that I going to try to uses, and maybe you can look at the formula to see what i'm trying to acomplish. Please review.



http://cid-03d2da80fac69eb8.skydrive...TESTSHEET1.xls

P.S. I notice the COUNTIFS formula, but does it matter if the version of Excel plays a factor in this?

Oooops - You're right - COUNTIFS is only for 2007

OK - Lets try a different method:

and
Using your sheet (and testing in both 2003 AND 2007) - it all works fine.

Let me know if it's working for you!

It works!!!

Thank you so, so, so, much! You rock!

Can i relay on you for future projects that i might have? Lol

Sure - I love to practice at every opportunity!

L8R M8!