Calculate Filtered Columns

Excel 2007
How can I calculate columns when I use the filter?
The calculated numbers do not always match what they should be.
0
Utf
4/6/2010 10:50:03 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
892 Views

Similar Articles

[PageSpeed] 10

Look up the SUBTOTAL function in help

"Canon" wrote:

> Excel 2007
> How can I calculate columns when I use the filter?
> The calculated numbers do not always match what they should be.
0
Utf
4/6/2010 11:00:01 AM
SUBTOTAL Function
http://www.ozgrid.com/Excel/excel-subtotal-function.htm


-- 
Regards
Dave Hawley
www.ozgrid.com
"Canon" <Canon@discussions.microsoft.com> wrote in message 
news:A3188127-CB8C-4DD8-BDCE-98916FB07411@microsoft.com...
> Excel 2007
> How can I calculate columns when I use the filter?
> The calculated numbers do not always match what they should be. 

0
ozgrid
4/6/2010 11:09:16 AM
The column I want to calculate is always the same, but I have to change the 
values of the filter, and I would like it to automatically calculate each 
time I do this.

"RonaldoOneNil" wrote:

> Look up the SUBTOTAL function in help
> 
> "Canon" wrote:
> 
> > Excel 2007
> > How can I calculate columns when I use the filter?
> > The calculated numbers do not always match what they should be.
0
Utf
4/6/2010 11:18:01 AM
Ronaldo understood you. The solution is the Subtotal function. You want to 
total the visible cells. Help explains it well.

Regards,
Fred

"Canon" <Canon@discussions.microsoft.com> wrote in message 
news:C689222A-B79C-4228-B5CA-73A52B140773@microsoft.com...
> The column I want to calculate is always the same, but I have to change 
> the
> values of the filter, and I would like it to automatically calculate each
> time I do this.
>
> "RonaldoOneNil" wrote:
>
>> Look up the SUBTOTAL function in help
>>
>> "Canon" wrote:
>>
>> > Excel 2007
>> > How can I calculate columns when I use the filter?
>> > The calculated numbers do not always match what they should be. 

0
Fred
4/6/2010 11:26:39 PM
Reply:

Similar Artilces:

Copying data to a blank column
I would like to copy data from 1 column in sheet1 starting at row 12 to the first empty column starting at row 8 in sheet2. sub trythis()'SAS untested sc=2 slr=sheets("sheet1").cells(rows.count,sc).end(xlup).row with sheets("sheet2") dlc=.cells(1,columns.count).end(xltoleft).column+1 sheets("sheet1").cells(12,sc).resize(slr) copy .cells(8,dlc) end with end sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Ed Davis" <ed.davis1@verizon.net> wrote in message news:6F9BC9EC-E48E-42CC-84D8-01C007B0C6E8@micr...

MONEY06 Return of Capital Calculation
The Return of Capital activity for Investment is a good one, except: I enter a positive return of capital when mutual funds do a return of capital distribution. I also need to use the Return of Capital activity for reinvestment of distribution. For this I enter a NEGATIVE return of capital. The Portfolio view cvalculates the positive and negative return of capital correctly as can be demonstrated with the adjustment in the cost basis after a return of capital. However, if one sells the investment and go to capital gains report. The Cap gains report ignores the negative return of ca...

Calculated field IM
Hi I am writing an integration to bring in Receivables Transaction in GP V9 I would like to be able to have a script attached to the Amount field to add 2 fields on my source file to get the value for the Amount. Don't seem to get the syntax correct - can anyone help? thanks Jean In the field script, you can use something like this: CurrentField = SourceFields("SOURCE.FIELD1") + SourceFields("SOURCE.FIELD2") Where SOURCE is the name of the data source, and FIELDx is the name of the field in the data source. You don't need SOURCE if you only have 1 data sour...

Default Filter Unavailable
When attempting to run reports in CRM, I am receiving the following error message. ERROR: This report cannot have a default filter ...

What is the formula to convert a whole column containing date to text?
What is the formula to convert a whole column of date to text? If I am to do it cell by cell, I need to press F2 then put a --> ' in front of the date. Is there a way I can do it with formula? I have tried CONCATENATE to combine column a which contains ' with date 12/31/03. Column A Column B ' 12/31/03 =CONCATENATE(A1,B1) However, the end result I got is as follow. '37986 instead of '12/31/03 Please help! Hi Caine one way: =TEXT(A1,"mm/dd/yyyy") where A1 stores your date value. Copy this down ...

Convert QuickBooks time format to excel to calculate
When I export a Quickbooks "Time by Job Summary" report to Excel, the time is converted in a time and date format. If I want to put a dollar amount to that time, it needs to be converted into a number format. I can easily convert the time for anything less than 24 hours using =(D18-INT(D18))*24. How can I easily do that for any time greater than 24 hours? Give some examples of the type of data you want to convert. Are you trying to get the date into one column and the time (in Excel format) in another column, or are you saying that a time of say 5.5 days is showing up a...

Autoflow, columns and multiple pages
I'm using Publisher 2000 and here's something I've never been able to figure out; how do I set up a multiple page document, each page with 2 columns and copy text so that Publisher will automatically flow into the columns but link the columns together for multiple pages? Or is that even possible? Thanks in advance! MV >-----Original Message----- >I'm using Publisher 2000 and here's something I've never been able to >figure out; how do I set up a multiple page document, each page with 2 >columns and copy text so that Publisher will automatically flow int...

entering 0 to a range of data in a column
How do i Prefix 0 to all the data in a perticular data. Eg. I have different No. like 1234, 3432, 3453 etc in a column and want to see that it shows up as 01234,03432,03453 If it is just for display just use a custom format like 00000, if you need 5 characters in the cell you can use a help column and then use =TEXT(A2,"00000") copy down, edit>paste special as values in place, delete original column -- Regards, Peo Sjoblom "Manoj Nair" <Nair,Manoj@kuwait.army.mil> wrote in message news:90A3390A-3C10-4967-A639-80C0680C7E45@microsoft.com... > How do i Pre...

RECEIVED & SENT Column Confusion
I see you can display the SENT column optionally in Outlook 2003. But strangely, the SENT column shows a time newer than the RECEIVED column. Like a message will say SENT 9:59 PM & RECEIVED 9:31 PM. What's up with that? Thanks, BTJustice one of the mail servers or computers has the wrong time. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions...

I have a column of numbers, some negative, some positive
I have some calulations in my worksheet. They are done twice, if my number is positive or negative. I need to fill the cell next to my number with the calulation that correspondes with the + or - of my number. if A1 + if A1 - A1 B1 C1 D1 -10 7 12 A2 B2 C2 D2 14 4 6 I need the 12 to appear in B1. and the 4 to appear in B2. Thanks In B1 enter =IF(A1<0,D1,C1) Copy down. Gord Dibben M...

Sum a column if two criteria are met
I need a formula that sums column C for each "Loc" by "Item"? A B C LOC Item WA Txn Value 1241 R3331 595.20 1241 R3334 595.20 1241 R3334 1,488.00 1242 R1400 908.46 1242 R1400 908.46 1242 R3334 1,488.00 1242 R3334 1,190.40 1243 R1400 908.46 1243 R3334 297.60 -- swestberry Hi, Try this =SUMPRODUCT((A2:A20=1241)*(B2:B20="R3334")*(C2:C20)) You could use cell ref's for the lookup values instead of having them embedded in the formula =SUMPRODUCT((A2:A20=D1)*(B2:B20=D2)*(C2:C20)) -- Mike ...

Scatter chart with columns instead of points
I need to create a chart showing the number of students who received each numerical grade on test. The spacing between grades is not equidistant (ex: 65, 70, 90, etc.) and I need to show the true spacing on the x-axis. So I chose a scatter chart because the other charts make the points of equal distance on the x-axis. The trouble is I need to represent the data as columns so if two students scored a 65 and eight scored a 90, the 90 column would much higher. Is there a way to add vertical columns to a scatter chart? Put your grades in a column, and include the grades which had none. ...

Show column header in addition to min/max result
Hello everyone Every month I work out the max and min for a range of income types over the last 24, 18, 12 and 6 months. As well as displaying the max and min result, is there a way to also select which of the months was the max and which was the min so it can be displayed alongside the value? The month is the column header. You didn't post your formula. Have a look in the help index for INDEX -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Tabbi" <Tabbicat22@gmail.com> wrote in message news:bbc50392-0d13-4020-97df-98c238352b4d@e25g200...

Database Diagram Column notes
Is there any way to display the notes from the column properties in my drawing? ...

Trying to calculate the average cost per minute of cell phone usage
I have a spreadsheet that I�m trying to use to calculate the cost per minute of cellular calls. I have 3 plans (in rows 3, 4, and 5) that each provide a monthly cost (column a), and the number of minutes included in that cost (column b). There is also a cost per minute if you go over that allowance (cell E2). I have a field (cell C7) that I use to enter the average number of minutes used. I have a drop down list (cell C8) that allows you to select the plan you have. I have a formula that is currently written as: =SUM(((C7-C8)*E2)+A5)/C7 where: C7 = average number of minutes...

Filter & merged sells borders
Hi, After filtering I`ve got no border for merged sells. How can I solve it? Regards Dmitry Merged cells can cause problems when sorting or filtering. If you're merging cells vertically, perhaps you could unmerge them, and enter a value in each row. Then, use conditional formatting to hid the duplicate entries, as described here: http://www.contextures.com/xlCondFormat03.html#Duplicate Fadei wrote: > Hi, > > After filtering I`ve got no border for merged sells. How can I solve > it? > > Regards > Dmitry > -- Debra Dalgleish Contextures http://www.co...

custom views-no available values in 'edit filter criteria'
hi there, i am trying to create a custom view that is based on one of the values in a picklist within a custom field. when i go into edit filter criteria, i select the field i want, choose equals and then when i get to 'enter value' i have no options in the 'available values' area. I gather i should have the picklist values as available values to choose from? I tested it using another standard field; marital status then i have options in the available values area. but i had added another 2 options to the standard picklist-de-facto and separated; which do not appear in va...

calculate age from birth date
How can I calculate employees age from birthdate as of todays date? Thank you, M Burton Hi you have two responses to your original post in microsoft.public.excel.worksheet.functions if you can't locate them, post back and i'll copy & paste the responses for you. Cheers JulieD "frc" <frc@discussions.microsoft.com> wrote in message news:9B61A612-198C-4E66-82E2-40DD219DCE95@microsoft.com... > How can I calculate employees age from birthdate as of todays date? > Thank you, > M Burton ...

Dynamic Date Calculation
Hello is there a formula that will allow me to show the current age of someone, based on what is entered as their birthday. So the columns would be Member age B'day Formula here Typed in manually here. It would preferable for the Member age to be dynamic, so that as time progresses, the age will update automatically....if that is possible. Thanks for any suggestions. Hey Mike C you could use this formula, where the birthday is in B2: =YEAR(TODAY())-YEAR(B2)- IF(TODAY()>=DATE(YEAR(TODAY()),MONTH(B2),DAY(B2)),0,1) I guess there's a shorter formula, but at least it w...

how to skip a column when entering data?
On one worksheet, for the sake of appearance Column B contains a calculated amount, which is the product of Column A and Column C. Is there a way to skip to column C when entering the data in Column A without doing it manually? Thanks, Godzilla This will give you the idea. right click on sheet tab>view code>insert this>SAVE Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Row > 2 And Target.Column = 1 Then ActiveCell.Offset(, 2).Select End Sub "Godzilla" <Godzilla@monster.org> wrote in message news:MPG.19e3d6a7ae92e03398971a@news....

How can I get the sum of a column with an arbitrary number of rows?
I am trying to get one cell to return the value of an entire column on another worksheet for which the last row is always changing. If I use: =SUM(Sheet1!$A$2:A25) Then the cel will be broken once I add a row 26 to Sheet 1. What I would like to do is create a sum of the column no matter how many rows there are. Thanks. "Mike F." <nospam@spamless.com> wrote in message news:4coomvgodg9lvcp66cp9bddcrnhov8ap00@4ax.com... > I am trying to get one cell to return the value of an entire column on > another worksheet for which the last row is always changing. If I use: >...

Auto size columns
Hello! I want to autosize my coloumns which is imported through ExcelMl (xml) file, by setting some property in the xml file. Does anyone now how to achieve this? Thanks in advance! ...

Counting values in an array where value in one column is not null
Hi, I am collating project details from multiple sources and need to report back how many projects are active in specific phases based on data such as in the table below: Projects Phase Version "Start Date W/c" "End Date W/c" A Prep Build Deliver a1 5-Apr 26-Apr Close a1 26-Apr 26-Jul B Prep Build Deliver b1 5-Apr 26-Apr Close b1 26-Apr 26-Jul C Prep Build c1 19-Apr 3-May Deliver c1 5-Apr 19-Apr Close c2 3-May 2-Aug D Prep d1 5-Apr 5-Jul Build d1 26-Jul 2-Aug Deliver d1 5-Jul 26-Jul Close d1 2-Aug 1-Nov If the start ...

Chart problem: values in column A doen't show on x-axis
The data in column A in the table I want to use for a chart contain numeric values: 2000 - 2001 - 2002 - ... In the chart these don't show as labels for the x-axis, but as a serie of data in the chart. Any way around this without changing all the values in coumn A int text -- digica ----------------------------------------------------------------------- digicat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1492 View this thread: http://www.excelforum.com/showthread.php?threadid=49945 ...

Return activie cell indicator to column A next row down.
I enter data across 24 (A - X) columns in the same row. I then return to column A, to enter data across the same 24 columns one row beneath the completed row. How can I have Excel return to colume A and move down 1 row automatically after I have entered data in the last (column X) column? Thanks for any help. -- Dewayne If you select your range first (say A2:X99), then you could just hit enter (or hit the tab key) to go across (and then down). To make the enter key work, turn: tools|options|edit tab|change move selection after enter to right (or turn it off) Dewayne wrote: > >...