extracting totals from within a spreadsheet

I need to know how to take different information from within a spreadsheet 
--where two conditions/catogories must apply so that a third column where i 
have inputed hours, will total for those conditions only... can anyone help?
0
Jovetta (4)
1/5/2006 6:07:02 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
940 Views

Similar Articles

[PageSpeed] 35

Please spell out what you want we are not mind readers
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jovetta" <Jovetta@discussions.microsoft.com> wrote in message 
news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com...
>I need to know how to take different information from within a spreadsheet
> --where two conditions/catogories must apply so that a third column where 
> i
> have inputed hours, will total for those conditions only... can anyone 
> help? 


0
bliengme5824 (3040)
1/5/2006 6:39:43 PM
=SUMPRODUCT(--(A2:A50="x"),--(B2:B50="y"),C2:C50)

will sum C2;C50 where A2:A50 is "x" AND B2:B50 is "y"

adapt to fit your requirements

if you have excel hours you might want to use a custom format of [hh]:mm or
else it will start anew after 24:00


-- 

Regards,

Peo Sjoblom

"Jovetta" <Jovetta@discussions.microsoft.com> wrote in message
news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com...
> I need to know how to take different information from within a spreadsheet
> --where two conditions/catogories must apply so that a third column where
i
> have inputed hours, will total for those conditions only... can anyone
help?


0
terre081 (3244)
1/5/2006 6:57:24 PM
I am still confused.  

i will try to explain it this way.
If column C is true
and column E is true
and column D is true
then i want the total of column C and and a total for column D

Does that help? 


"Peo Sjoblom" wrote:

> =SUMPRODUCT(--(A2:A50="x"),--(B2:B50="y"),C2:C50)
> 
> will sum C2;C50 where A2:A50 is "x" AND B2:B50 is "y"
> 
> adapt to fit your requirements
> 
> if you have excel hours you might want to use a custom format of [hh]:mm or
> else it will start anew after 24:00
> 
> 
> -- 
> 
> Regards,
> 
> Peo Sjoblom
> 
> "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message
> news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com...
> > I need to know how to take different information from within a spreadsheet
> > --where two conditions/catogories must apply so that a third column where
> i
> > have inputed hours, will total for those conditions only... can anyone
> help?
> 
> 
> 
0
Jovetta (4)
1/5/2006 8:08:04 PM
Jovetta,

See inline.

Jovetta wrote:
> I am still confused.  
> 
> i will try to explain it this way.
> If column C is true
> and column E is true
> and column D is true,

If I understand you correctly (& I'm not at all sure that I do), you're 
trying to apply some kind of conditional formula: IF(<some condition is 
true>, <value or action if true>, <value or action if false>).
It appears that you have multiple conditions to apply.  If this is 
correct, look up IF(), AND(), and OR()functions in Help. Use IF(AND()) 
if ALL conditions must be met; use IF(OR()) if ANY condition may be met.

> then i want the total of column C and and a total for column D

This is confusing.  Do you mean you want to SUM the amount in each 
column? Or sum the totals from each column? Or most probably, do you 
want to sum the amount in column C, row x, plus the amount in column D, 
row x and show the results in, say, column Q, row x?

I suspect this is an action that you will want to perform multiple 
times, varying the criteria each time. In that case, you might want to 
link your spreadsheet to MS Access and build your query there, or use 
the MS Query Wizard (PS. MS Query comes bundled with Excel as an 
optional feature; you may need to install it).

LeAnne
0
nospam2791 (369)
1/11/2006 7:15:21 PM
Reply:

Similar Artilces:

Excel automation: how to open a blank spreadsheet
Hello, When using Excel automation: ..Open command requires the full path to the file (workbook) I need to open just blank, unnamed spreadsheet. How to do that? Thanks, Claire "Claire" <replyto@fra> wrote in message news:eD4hhm4BLHA.5584@TK2MSFTNGP06.phx.gbl... > Hello, > When using Excel automation: > .Open command requires the full path to the file (workbook) > I need to open just blank, unnamed spreadsheet. > How to do that? > Thanks, Claire I have found that doing: ..Visible = True and then using .Add it will s...

Adding email addresses to contacts contained within an email message
This is a multi-part message in MIME format. ------=_NextPart_000_000E_01C3F079.723AEFC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Does anyone know of either a macro or utility that would update all = email recipients contained in the body of an email message to the = contacts? TIA, Alan ------=_NextPart_000_000E_01C3F079.723AEFC0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD>...

Wrapping sheets within a workbook
I am working with raw data in Excel 2003 in a workbook where there are 3-5 sheets completely filled with data, all 65536 rows. There are duplicates scattered throughout the sheets. Is there a way I can link all the sheets together so that when I do a sort, it will sort all the rows on each sheet within the workbook? You would need a macro to do that. This is an example of a Merge Sort algorithm - you would need to sort each individual sheet, and then you could look at the topmost item in each sheet and decide which record should be written to a new sheet (and adjust a counter for the sh...

Deducting minutes off running total
Hi There, I am currenly trying to keep a tab of my staff's extra mins/hours worked which they can use at any stage. I currently have two columns a running total of additional hours worked and a column showing hours deducted/ taken I would like to deduct the hours taken from one cell against another e.g (CELL H40) 3hrs - (CELL G46) 45mins = 2.15 How do i get it so that my (CELL H48) shows remaining hours left to use is 2.15? I look forward to your response. Does H40 actually contain "3hrs" or does it contain the number 3 ? Similarly, does G46 contain the ...

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

How do I separate address information from within an Excel cell?
I have a Christmas Card list that was created using a single cell for the whole address. Street, City, Zip/Postal etc. I need to split those components out into separate cells in order to do a proper mail merge. Is there any way to do this. I am not completely unfamiliar with macros but I if that is the solution I would require a fair bit of hand holding. Thanks S. If you actually used commas to separate the fields (and none of the fields contain commas), you could select the column and do: Data|text to columns delimited comma Remember to keep enough open columns to the right s...

Report totals and grouping
I have an access 2003 file that looks like this: Land Vehicle Water vehicle Air vehicle Car Boat Plane Bike Yacht Jet Truck Raft Blimp Car Yacht Plane Bike Raft Jet Bike Boat Plane etc. I'd like the report to look like: Land Vehicle Bike 3 Car 2 Truck 1 Water Vehicle Boat 2 Yacht 2 Raft 3 Air Vehicle Plane 3 Jet 2 Blimp 1 I cannot figure out how to do this. Any suggestions are appreciated. Therm...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

Inclusive number of days and running total
Hi, I have a column of start dates (B) and a column of end dates (C). I would like to have the inclusive total of days. I would then like to have a running total of days for the callendar year as well as the total for any year and a grand total. Have been playing with DATEDIF but no luck so far. Cheers, Chris Just subtract the date in C from the date in B and format as General. Then SUM as required. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Chris" <Chris@discussions.microsoft.c...

Using VLOOKUP from within a pivot table
Hi everyone, I'm new to this forum and apologize that I haven't had time to read the FAQ's. I'm working on a deadline and need some help ASAP. Now that I'm a member, though, I will read through the FAQ's because I anticipate I'll be needing more of your help in the near future. I'm pretty sure this isn't the type of question that's been beaten to death in any case... I'm working a with a PivotTable that pulls data from an ODBC datasource. The table is a list of sales agents on the vertical axis and a the number of purchases made by clients on the ...

how to delete alternate rows in a spreadsheet?
hello, i have a spreadsheet with about 60,000 data points in it, Excel will only let me plot 32,000 points! Is there a way/formular so i can delete everyother row? ie,so instead of having rows numbered 1,2,3,4,5,6,7,8,9,10. I would end up with 1,3,5,7,9 and so on! This would give me around 30,000 points! Thanks, Simon. In a helper column put this: =MOD(ROW(),2) Autofilter the data and select the value that you want to delete and delete it. Then delete the helper column. -- HTH, Barb Reinhardt "SI" wrote: > hello, i have a spreadsheet with about 60,000 data ...

Secondary Value within Bar
I am trying to set up a simple bar chart. Example: Total$ 2003, 2002, 2001. Within each of those bars I need to fill the bar up with another value. Example Total$ was 10000, but 5000 when towards another category. I need to show the total as 10000, but fill the bar up to 5000 with another design. Make sense? Pat - You could make a stacked bar (or column) chart. In these you do not plot the total, but all the constituents of the total. If you don't know them all, use 'Other' and use a formula to subtract all the known constituents from the total. So your chart would ha...

Removed content in right columns and limited size of spreadsheet
I was trying to get rid of the contents in the columns to the right of my spreadsheet and instead I accidently deleted everything to the right (area is now grey). Now I can't insert new columns because I have limited the size of the spreadsheet. How can I recover columns to the right? I can't just click undo because I have saved and gone out of the spreadsheet. The data in deleted columns is lost I do not understand how you think you have limited the size of the worksheet That is not possible tell us more best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/...

total size of files
I need a script to find out the number & total size of files based upon the file extention across about 30 servers. I'm searching for mdb files. The script will be run remotely against local drives on these servers. Thanks On Nov 18, 10:19=A0am, Tom1 <usernetu...@yahoo.com> wrote: > I need a script to find out the number & total size of files based > upon the file extention across about 30 servers. I'm searching for mdb > files. The script will be run remotely against local drives on these > servers. Thanks <warning> <aircode> with ...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

Is there any way to get rid of a link in a spreadsheet?
I copied a wooksheet to keep a format but changed the content. The original worksheet was linked to another. The new one is not but I can't get rid of the link. Couldn't find anything in help. Any way to get rid of a link? You need to find the link and delete the reference. To replace the reference in formulae you can either delete the entire formula or use Edit/Replace and replace the file reference with "". (It helps if the link-to file is open as the reference won't include the path. If it is in a named range or formula and there are many the process gets cu...

extracting hours from a sum of time
I have a cell which sum a bunch of cells containing an elapsed time. The sum totals to more than 24 hours. I have to a cell with a cost per hour and I need to caclculate the total cost. The problem is that the hour function returns values in the range of 0-24. My current sum is 25:30 and the hour function return 1 and not 25. Any ideas how to bypass it? It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will. To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and a day is s...

Spreadsheet cleaning
I downloaded info from another program to a spreadsheet. I am trying to clean up the data so I can use it. Please let me know if there is a way to do the following preferably without VBA. Post either solution but I'm just not too comfortable with VBA yet. What I want to do is to seek out every cell with an "*" and have Excel delete the entire row. Doing a sort would not be an option because the order of the data is important for my needs. Thank -RA Add a new column (say column A) Put 1 in A1 and put 2 in A2 select A1:A2 and drag it down your data using the autofil...

Extract Text
Dear all I have this text in a field: 200701-I 200701-II 200701-III 200701-IV and I need to separate only the data after the six first data: -I -II -III -IV How can I do it? Thanks a lot!!! Andr=E9. Take a look at Access HELP for the Mid() function. Open a query in design view and add a new field something like: NewField: Mid([YourField],7) -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ <gatarossi@ig.com.br> wrote in message news:11893397...

Can I extract unique cell values from every nth column?
I have a range that covers B5:XA160. For each row, data is entered in 11-cell groups: date, some numbers, description (a text value), more numbers, and more numbers. At first it was enough to merely count how many times certain descriptions appeared, because those were the only ones we would see - or so the story went. Now, I need to extract the unique descriptions AND provide a count! Actually, I'm cheating a bit. I'm using SUMPRODUCT to return the number of times the expected descriptions appear, and by subtracting these from the total number of text values I get a count of &quo...

How to do this total?
I am using Office 2007, but save my files in the older format for others. How do I do this formula? In Column D I have prices. Right now it goes D16 - D285. In Column E I put the quantity I sent to someone. At the bottom I want a total $$. So, for example: if E16 = 1 then check price in D16 and add to total. If E17 = 2 then check price in D17 and add to total twice, etc. all the way down. =SUMPRODUCT(D16:D285,E16:E285) HTH, Bernie MS Excel MVP "shawn" <yuppicide138@removethisoptonline.net> wrote in message news:%23zUmVSkVKHA.4780@TK2MSFTNGP05.phx.gbl... >I am us...

Report totals
I'm using Access to build a database at work. I am entering data on a daily basis from invoices that I wish to sumarize by various periods of time (daily, weekly, monthly, etc....) so have built From and To parameters into my query. When running reports, I get #Error for the report totals. If I build my dates into the query as >=09/01/2007 And <= 09/30/2007 I can get a total on the report. The query works with the parameters, but not the report. I am using the =DSum function in the report footer to obtain my totals. Any ideas what I am doing wrong? -- ComputerStudent T...