#1




Need help with a formula
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. Last edited by Bird_FAT; 04222009 at 11:56 PM. 
#2




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 
#3




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 
#4




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.

#5




Quote:
So you are only looking to count if there is ANYTHING in the row  OK Code:
=COUNTA(*RANGE*) 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: Code:
=COUNTA('TECH ONLY'!E5:E46) If you were looking for how many times the name Phil were to appear in the First Name column on the TECH ONLY worksheet... Code:
=COUNTIF('TECH ONLY'!C5:C46,"Phil") 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! 
#6




Thanks BIRD!
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!

#7




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. 
#8




No Worries M8!
No worries!
Code:
=COUNTIFS(Sheet1!RANGE1,"NAME",Sheet1!RANGE,"CONDITION") 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' eg Code:
=COUNTIFS(Sheet1!A2:A21,"Bill",Sheet1!B2:B21,"Tech") Code:
=COUNTIFS(Sheet1!A2:A21,"Bill",Sheet1!B2:B21,"Pend") Have fun! 
#9




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://cid03d2da80fac69eb8.skydrive...TESTSHEET1.xls P.S. I notice the COUNTIFS formula, but does it matter if the version of Excel plays a factor in this? 
#10




Another method
Oooops  You're right  COUNTIFS is only for 2007
OK  Lets try a different method: Code:
=SUMPRODUCT(((Sheet1!A2:A20)="CAIN")*((Sheet1!B2:B20)="TECH")) Code:
=SUMPRODUCT(((Sheet1!A2:A20)="CAIN")*((Sheet1!B2:B20)="PEND")) Let me know if it's working for you! 
#11




It works!!!
Thank you so, so, so, much! You rock! Can i relay on you for future projects that i might have? Lol 
#12




Quote:
L8R M8! 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Excel Formula Help  masoom84  Excel  1  03072009 09:41 AM 
Excel Formula Help  Shahzad  Excel  1  12072008 04:13 AM 
formula in excell  curlymal  Excel  2  02092007 11:34 AM 
Mailmerge Formula  mridley  Mail Merge  0  07272006 03:16 AM 
Stuck on this formula!!!!  CADmanJP  Excel  2  03102006 02:06 AM 