#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; 04-22-2009 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://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? |
#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! |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel Formula Help | masoom84 | Excel | 1 | 03-07-2009 09:41 AM |
Excel Formula Help | Shahzad | Excel | 1 | 12-07-2008 04:13 AM |
formula in excell | curlymal | Excel | 2 | 02-09-2007 11:34 AM |
Mailmerge Formula | mridley | Mail Merge | 0 | 07-27-2006 03:16 AM |
Stuck on this formula!!!! | CADmanJP | Excel | 2 | 03-10-2006 02:06 AM |