if function 12-14-09

if I have 500 rows and several col

and lets say col B is office cities 

IE Dallas, Fort Worth, Austin, Houston 

and the col F 
has sales totals
Is there a way I can subtotal the col f in setter work sheet 
IE 
if col b = Dallas, subtotal everthing that = dallas by col F 
0
Utf
12/14/2009 9:28:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
766 Views

Similar Articles

[PageSpeed] 47

You want a Pivot Table. You'll love them once you get used to them. Google 
pivot tables for more information.

Regards,
Fred

"Dylan lotz" <Dylan lotz @discussions.microsoft.com> wrote in message 
news:AFDEAF5E-B191-45EC-8569-634F92552859@microsoft.com...
> if I have 500 rows and several col
>
> and lets say col B is office cities
>
> IE Dallas, Fort Worth, Austin, Houston
>
> and the col F
> has sales totals
> Is there a way I can subtotal the col f in setter work sheet
> IE
> if col b = Dallas, subtotal everthing that = dallas by col F 

0
Fred
12/14/2009 10:27:04 PM
Hi,

Sort in ascending order on column B and then use Data > Subtotal.

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dylan lotz" <Dylan lotz @discussions.microsoft.com> wrote in message 
news:AFDEAF5E-B191-45EC-8569-634F92552859@microsoft.com...
> if I have 500 rows and several col
>
> and lets say col B is office cities
>
> IE Dallas, Fort Worth, Austin, Houston
>
> and the col F
> has sales totals
> Is there a way I can subtotal the col f in setter work sheet
> IE
> if col b = Dallas, subtotal everthing that = dallas by col F 

0
Ashish
12/14/2009 11:27:33 PM
could you elaborate, Data>subtotal:

Please help

col b; has several city names, col c wiht emplyee names ect.

Then col F has the employess sales totals,  we constanctly add employee lines,
so it would be much easier to say if colb=dAllas sum all totals associated 
w/ col F.

My staff is not good enough to this in a pivot tabel. dont know is If I 
could inorporate this in a =stringconcat vba formula to consolidate email 
address assoiciated with the email in rows for the employess 
pleae advise


"Ashish Mathur" wrote:

> Hi,
> 
> Sort in ascending order on column B and then use Data > Subtotal.
> 
> -- 
> Regards,
> 
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
> 
> "Dylan lotz" <Dylan lotz @discussions.microsoft.com> wrote in message 
> news:AFDEAF5E-B191-45EC-8569-634F92552859@microsoft.com...
> > if I have 500 rows and several col
> >
> > and lets say col B is office cities
> >
> > IE Dallas, Fort Worth, Austin, Houston
> >
> > and the col F
> > has sales totals
> > Is there a way I can subtotal the col f in setter work sheet
> > IE
> > if col b = Dallas, subtotal everthing that = dallas by col F 
> 
0
Utf
12/15/2009 4:17:01 PM
Hi,

Create a pivot table.  Drag column B to the row area and column F to the 
data area.

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"dlotz@uafc.com" <dlotzuafccom@discussions.microsoft.com> wrote in message 
news:B63D7A9F-86C4-47B8-AA5C-97BF34A6F4E8@microsoft.com...
> could you elaborate, Data>subtotal:
>
> Please help
>
> col b; has several city names, col c wiht emplyee names ect.
>
> Then col F has the employess sales totals,  we constanctly add employee 
> lines,
> so it would be much easier to say if colb=dAllas sum all totals associated
> w/ col F.
>
> My staff is not good enough to this in a pivot tabel. dont know is If I
> could inorporate this in a =stringconcat vba formula to consolidate email
> address assoiciated with the email in rows for the employess
> pleae advise
>
>
> "Ashish Mathur" wrote:
>
>> Hi,
>>
>> Sort in ascending order on column B and then use Data > Subtotal.
>>
>> -- 
>> Regards,
>>
>> Ashish Mathur
>> Microsoft Excel MVP
>> www.ashishmathur.com
>>
>> "Dylan lotz" <Dylan lotz @discussions.microsoft.com> wrote in message
>> news:AFDEAF5E-B191-45EC-8569-634F92552859@microsoft.com...
>> > if I have 500 rows and several col
>> >
>> > and lets say col B is office cities
>> >
>> > IE Dallas, Fort Worth, Austin, Houston
>> >
>> > and the col F
>> > has sales totals
>> > Is there a way I can subtotal the col f in setter work sheet
>> > IE
>> > if col b = Dallas, subtotal everthing that = dallas by col F
>> 
0
Ashish
12/15/2009 11:17:35 PM
Reply:

Similar Artilces:

I am looking for a function for linear interpolation
I am looking for a function for linear interpolation Look in HELP for the LINEST() function -- Kind regards, Niek Otten Microsoft MVP - Excel "azad" <azad@discussions.microsoft.com> wrote in message news:C53A752C-DF76-4B9F-9145-6F39495088B7@microsoft.com... >I am looking for a function for linear interpolation ...

Adding if to Average If Array function
Hi, I recently had help understanding the following formula {=IF(ISERROR(AVERAGE(IF(Score>0,Score))),"",AVERAGE(IF(Score>0,Score)))} Thanks to Luke M, Pete UK and David Biddulph who provided the explanation. I've now been asked to add to this formula so that if either Q3 or Q4 is greater than 4 then the average cannot be lower than 3. Q1:Q35 is the named range score. This is really complicated because I still need to have an average and I'm thinking that the If part is non array but the average part still needs to be an array. Can you mix and match ...

Can I use the NOW() function in a formula?
I have a cell B1 that has NOW() time and another cell B2 that has NOW() date. I am trying to have data from another cell F1 brought in to the destination cell B3, when a given date and time occur. =(IF(AND(B1="23:00:00",B2="12/03/2009"),F1," ") This formulas does not work. The Clock is continuously active / always changing. Any suggestions would be appreciated. Thanks. The NOW() function returns both a date and a time. Even if you format the cell to display only date/time, all the data is still retained. NOte that if you did want just the d...

Outlook 2007 12-13-09
How do you display Tasks in To-Do Bar using .pst file? "spikey789" <spikey789@discussions.microsoft.com> wrote in message news:A3D07AF5-AB82-4F01-9153-4D097DD392E2@microsoft.com... > How do you display Tasks in To-Do Bar using .pst file? Don't quite understand where the "pst file" bit comes in, but have you right-clicked on "To Do Bar" and made sure that Tasks is checked? ...

Sum function
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I am trying to put together a spreadsheet for a business plan and am having problems with the autsum and autofill functions. Firstly, even though I do a simple autosum, if I change a value in a cell, the sum does not change. Secondly, if I try to autofill across columns, the values of those columns simply mirror the value of the first. In other words it copies it. I am at a loss how to get round it as I have been working w Excel for years and have never had this problem. I have experimented with a new worksheet with ...

Need help with correct function or input for this problem/scenario
I have a situation where I am creating a borrowing base for a company. (I am going to shorten numbers to make it easier.) There is a $300 loan. Type A loan can borrow up to $300. Type B loan can borrow up to $150. Type C loan can borrow up to $50. I need a formula to input in excel that will give me a borrowing base for type B and C loans. For example, to borrow in type B loans, I have to check 1-what's outstanding on the entire loan. 2-what's outstanding on type B loans. 3-Take those two numbers and figure out what can be advanced under the answer to those questions. (I.E. $2...

Migration #12
Hi, there. I want to do migration from Notes to Exchange 2007, Where can I find some resources about this, step by step guide is best. Thanks very much! ITUser, kejia.li@163.com Hi, This link should help you: http://www.microsoft.com/technet/interopmigration/collaboration/default.mspx Cheers Nathan "ITUser" <kejia.li@163.com> wrote in message news:EAFE3DF5-E4BA-4503-8115-894AF8AE108C@microsoft.com... > Hi, there. > > I want to do migration from Notes to Exchange 2007, Where can I find some > resources about this, step by step guide is best. > &g...

Using Filter string with IF function
Is there a way to use a filter string with the IF function? I have a spreadsheet that looks like this: Invoice No. Invoice Date Invoice Currency Invoice Value 1 dd/mm/yyyy USD 1234.56 65 dd/mm/yyyy USD 2345.67 74 dd/mm/yyyy EUR 3456.78 88 dd/mm/yyyy USD 4567.89 when I filter on the currency: is there a function such as if(filter_string="USD";TRUE;FALSE)??? Thanks Aref Maybe this: Assuming row 1 are t...

Combo Box 12-26-07
Is there a way to have more than one bound column in a combo box? No. What is it you are trying to do? Perhaps we can offer a suggestion. -- Dave Hargis, Microsoft Access MVP "Sharon" wrote: > Is there a way to have more than one bound column in a combo box? On Wed, 26 Dec 2007 07:02:00 -0800, Sharon wrote: > Is there a way to have more than one bound column in a combo box? No. Now if you tell us why you think you need more than one bound column, perhaps some one can help with an alternative solution. -- Fred Please respond only to this newsgroup. I do not reply to pe...

Outlook 2000 Error #14
| I have a problem with Micosoft Outlook 2000 SR1. I am able to recieve | e-mail but not send. | | When I send the e-mail it sits on my Outbox but is not delievered. | | If I use Outlook express, e-mail functions OK, so I am fairly certain | it is something to do with Outlook and not with my provider or SMTP | server. I have checked my account properties and authentication | ...

Macros #12
I didn't see a group for Excel Macros so I'm posting here. I created a macro, saved it in the workbook that I was working on, gave it a short cut key stroke and it all worked perfectly. When done with the exercise I deleted the macro from that workbook. When I open a new workbook the ctrl key still activates the macro. I can't find where or how I delete the short cut key combo for that macro? All help is appreciated. Thanks in advance. SteveR "Steve" <SteveR7138@msn.com> wrote in news:ebQ92OWsFHA.3604@tk2msftngp13.phx.gbl: > I didn't see a gro...

Another Issue With A Function
Ron Rothstein suggested and I used this function in my Excel 2003 Workbook: Public Function QBRushYds(W, Q) QBRushYds = Worksheets("Week" & W).Range("C2").Offset(Q).Value / 10 End Function In Worksheet Totals Cell B56 thru B59 I have the following four calculations: =TRUNC(QBRushYds(1,1)) =TRUNC(QBRushYds(1,2)) =TRUNC(QBRushYds(1,3)) =TRUNC(QBRushYds(1,4)) In Worksheet Week1 Cells C3 thru C6 I have the following values: 725 814 283 9 The function correctly calculates the values: 72 81 28 0 If I change any of the values in C3 th...

And function into If function
I´m trying to get this function to work. But I´m getting an error message. =If(and(100/I6)*I7>101); I7<I6; 1; 0) What the function is supposed to do is to detect peak values in chart table that are measure errors from a measuring intrument and count every peak as 1. This is how the function is ment to operate: If the percent of the difference of the rise from the former cell to the next cell in the sequence of data is larger than 101% AND the value of this cell is smaller than the former cell then true=1 False=0 Im using a norwegian version of excel and I´m no...

sum function usin an IF statement
Bit short on detail, but maybe =IF(rng>10,rng) as an array formula, committed with Ctrl-Shift-Enter or =IF(rng1>some_val,rng2) again an array formula, where rng1 and rng2 are the same size. -- HTH RP (remove nothere from the email address if mailing direct) "jimk" <jimk@discussions.microsoft.com> wrote in message news:2873BFCF-598E-4956-808C-31C1638741A9@microsoft.com... > ...

datestamp 06-09-07
How can I make a datestamp on the records in a table when they are modified or created? I am running Access 97? Thanks. "Wayne-I-M" <WayneIM@discussions.microsoft.com> wrote in message <FB22AFD2-08BD-4F02-A2BF-73577AD5B446@microsoft.com>: > Hi > > Create a new field in your table called DateStamp. > > Insert this new field into your form. > > Use the forms event OnCurrent > > Me.DateStampt = Now() > > Note this will alter the date stampt every time the form is > activated. if you want to keep a record of "each" time the...

How do I convert pounds to stones eg 200 pounds is 14 stone 4 lb
I have a table of weights, in pounds. How do I convert them to stones and pounds in excel. I dont want to say 14.5 stomes, I want to say 14 stones 7 pounds. Thanks Hi, With pounds in a1 try this =INT(A1/14) & " Stones " & MOD(A1,14)&" Pounds" Mike "J" wrote: > I have a table of weights, in pounds. How do I convert them to stones and > pounds in excel. I dont want to say 14.5 stomes, I want to say 14 stones 7 > pounds. > Thanks Try this: =TRUNC(A1/14) & " STONES " & ROUND(SUM(11/14,-TRUNC(11/...

Sum Function (was summing 13 cells to the left)
Trying to compose a formula that will only sum 13 cells to the left (13 months of data). When columns are added, I still only want to sum thirteens months of data. Can this be done without constanty changing my formulas? Make sense TIA Neil S. Hi see my reply to your old post -- Regards Frank Kabel Frankfurt, Germany Neil S. wrote: > Trying to compose a formula that will only sum 13 cells to the left > (13 months of data). When columns are added, I still only want to > sum thirteens months of data. Can this be done without constanty > changing my formulas? Make sense? ...

Rank Function? #2
Why would my RANK function be returning #DIV/0! ???? Didn't know this was possible. Anybody run into this before? Thanks!! > Didn't know this was possible It isn't - so Excel is saying that you are trying to do the impossible :) Your code will probably need to check for and exclude zero values -- Message posted from http://www.ExcelForum.com ...

Hlookup function
I am currently creating a page that has a column of calculations in it. The calculations are amounts of Future options. at the bottom of the column I calculate an average 6 month total by simply suming 6 months into the future. The question I have is how do I automate the Total sum number at the bottom, because every time I update my worksheet I have to redo all my calculations because the previous months value is now 0 because there is no future value anymore, so I have to rename my range and do my calculation over again. I originally thought of the Hlookup function but I can't get it to ...

Basic IF function help needed
This is a multi-part message in MIME format. ------=_NextPart_000_00AE_01C71C7B.8E24F3D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm using Excel 2002 for the first time and trying to place a function = into a cell. I 'm using IF(N44=3DL, M44-L44, L44-M44) . Which is supposed to mean If the letter L is in cell N44, then subtract = number in L44 from number in M44. Otherwise subtract M44 from L44. If have 2 main questions...First, how to get the result of the function = to show. Right now I see the function typed out in...

ByVal vs. ByRef 11-17-09
I guess I have fallen into the habit of generally passing values ByRef, unless I am certain that the method will never need to alter the value. Also I have never been quite sure what it means to pass a reference type ByVal. And I have never had a problem. Until this past weekend. This past weekend I was playing around with a TreeView representation of a directory and developed the code attached below. The code is very straightforward. A button click routine establishes the topmost node and then calls a recursive subroutine, Populate, to populate the rest of the tree. The ...

Windows Live Mail software client v.14.0.8089.0726
Can you answer these questions for me or address the issues being presented? Thank you kindly. 1.) When it concerns email encoding; What is the best setting? Right now I'm set to Western European (ISO) - should I stick with this or should I switch to Western European (Windows)? What's the difference. What's the best way to go? 2.) It sure would be nice to be able to directly email any questions I have to the powers that be at Windows Live Mail software technical support, all I have is a feedback form. How about a direct link to an email form for Windows Live Mail t...

Sum Function Without Including Hidden Rows?
I'm using Excel 2000 and have *hidden* several rows that include numeric data. I want to do a sum function on some of the columns intersecting those rows, but when I insert an AutoSum function, it includes data in the visible rows AND the hidden rows. Is there an easy way to perform a sum function that *only* includes the rows that are *-visible?-* (Which rows are hidden/visible are subject to constant change) Thanks for your help! --- Message posted from http://www.ExcelForum.com/ You would need a UDF for that or if there is a pattern (like if every other column/row is hidden) th...

How to use more than 7 IF functions in a formula?
I tried to post a question earlier but it never appeared on the messag list. Not sure if it went through. So here it is again. I am trying to set up a formula where when I type in a weight i another column it will automatically puts in an assigned value for tha weight. The problem is that the weights range from 110 through 260. have found that I cannot put in more than 7 IF functions in a formula. This is my first time trying to do something like this and I am havin problems. I know there has to be a way to do this. For example I tried this formula and it worked except I con only put ...

Function Keys and RMS POS screen
Hi all I wonder is there a way to reconfigure the Function keys in the POS screen For example instead of F1 being displaying Help screen, I want to make it to reprint last receipt or some other function. Does anyone want to show your pretty customized POS screen ?? I want to get and idea how people's POS screen looks like Thank you Regards, Joie Joie, There is not built-in way to make this happen, but we have made this work for other customers in the past. Due to the nature of the program though, this is nothing we have a commercial application for, and would have to make s...