Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-30-2009, 06:57 PM
tinkertron tinkertron is offline
Novice
Need help with a formula
 
Join Date: Mar 2009
Location: Garland, Texas
Posts: 15
tinkertron is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 03-30-2009, 09:00 PM
tinkertron tinkertron is offline
Novice
Need help with a formula
 
Join Date: Mar 2009
Location: Garland, Texas
Posts: 15
tinkertron is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 04-12-2009, 08:58 AM
Bird_FAT's Avatar
Bird_FAT Bird_FAT is offline Need help with a formula Need help with a formula Office 2007
Expert
 
Join Date: Apr 2009
Location: South East
Posts: 271
Bird_FAT is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 04-12-2009, 10:54 AM
tinkertron tinkertron is offline
Novice
Need help with a formula
 
Join Date: Mar 2009
Location: Garland, Texas
Posts: 15
tinkertron is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 04-12-2009, 11:18 AM
Bird_FAT's Avatar
Bird_FAT Bird_FAT is offline Need help with a formula Need help with a formula Office 2007
Expert
 
Join Date: Apr 2009
Location: South East
Posts: 271
Bird_FAT is on a distinguished road
Default

Quote:
Originally Posted by tinkertron View Post
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

Code:
=COUNTA(*RANGE*)
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:

Code:
=COUNTA('TECH ONLY'!E5:E46)
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...

Code:
=COUNTIF('TECH ONLY'!C5:C46,"Phil")
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!
Reply With Quote
  #6  
Old 04-16-2009, 07:23 AM
tinkertron tinkertron is offline
Novice
Need help with a formula
 
Join Date: Mar 2009
Location: Garland, Texas
Posts: 15
tinkertron is on a distinguished road
Smile 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!
Reply With Quote
  #7  
Old 04-16-2009, 07:57 AM
tinkertron tinkertron is offline
Novice
Need help with a formula
 
Join Date: Mar 2009
Location: Garland, Texas
Posts: 15
tinkertron is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 04-16-2009, 10:07 AM
Bird_FAT's Avatar
Bird_FAT Bird_FAT is offline Need help with a formula Need help with a formula Office 2007
Expert
 
Join Date: Apr 2009
Location: South East
Posts: 271
Bird_FAT is on a distinguished road
Cool No Worries M8!

No worries!

Code:
=COUNTIFS(Sheet1!RANGE1,"NAME",Sheet1!RANGE,"CONDITION")
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'

eg
Code:
=COUNTIFS(Sheet1!A2:A21,"Bill",Sheet1!B2:B21,"Tech")
This 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:
Code:
=COUNTIFS(Sheet1!A2:A21,"Bill",Sheet1!B2:B21,"Pend")
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!
Reply With Quote
  #9  
Old 04-16-2009, 11:51 AM
tinkertron tinkertron is offline
Novice
Need help with a formula
 
Join Date: Mar 2009
Location: Garland, Texas
Posts: 15
tinkertron is on a distinguished road
Default

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?
Reply With Quote
  #10  
Old 04-16-2009, 01:11 PM
Bird_FAT's Avatar
Bird_FAT Bird_FAT is offline Need help with a formula Need help with a formula Office 2007
Expert
 
Join Date: Apr 2009
Location: South East
Posts: 271
Bird_FAT is on a distinguished road
Default 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"))
and
Code:
=SUMPRODUCT(((Sheet1!A2:A20)="CAIN")*((Sheet1!B2:B20)="PEND"))
Using your sheet (and testing in both 2003 AND 2007) - it all works fine.

Let me know if it's working for you!
Reply With Quote
  #11  
Old 04-16-2009, 03:07 PM
tinkertron tinkertron is offline
Novice
Need help with a formula
 
Join Date: Mar 2009
Location: Garland, Texas
Posts: 15
tinkertron is on a distinguished road
Default

It works!!!

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

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

Reply With Quote
  #12  
Old 04-16-2009, 11:43 PM
Bird_FAT's Avatar
Bird_FAT Bird_FAT is offline Need help with a formula Need help with a formula Office 2007
Expert
 
Join Date: Apr 2009
Location: South East
Posts: 271
Bird_FAT is on a distinguished road
Default

Quote:
Originally Posted by tinkertron View Post
Can i relay on you for future projects that i might have? Lol

Sure - I love to practice at every opportunity!

L8R M8!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Formula Help masoom84 Excel 1 03-07-2009 09:41 AM
Need help with a formula 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
Need help with a formula Stuck on this formula!!!! CADmanJP Excel 2 03-10-2006 02:06 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:45 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft