Subtotaling count and sum

Can't find steps to subtotal a list showing both count and sum of
columns. Example, totaling a dollar column and also indicating the
number of transactions.  I can calc one or the other, but not both. I'm
sure it can be done, just don't know how.

Any suggestions appreciated....Hal


---
Message posted from http://www.ExcelForum.com/

0
1/27/2004 11:32:56 PM
excel 39879 articles. 2 followers. Follow

1 Replies
449 Views

Similar Articles

[PageSpeed] 29

Have a look at help for =subtotal().

It has a bunch of options for the first parameter.  One will count, one will
sum, one will countA and about 8 more!

=subtotal(2,a1:a10)   will count the number of numbers
=subtotal(9,a1:a10)   will sum the values

And =subtotal() will limit itself to the visible cells if you've applied
data|filter|autofilter (which is nice).



"Hal Plimpton <" wrote:
> 
> Can't find steps to subtotal a list showing both count and sum of
> columns. Example, totaling a dollar column and also indicating the
> number of transactions.  I can calc one or the other, but not both. I'm
> sure it can be done, just don't know how.
> 
> Any suggestions appreciated....Hal
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/28/2004 12:07:08 AM
Reply:

Similar Artilces:

counting the number of commas within a text string
Hi to All, Cell A1 contains a text string such as 56, 58, 12, 1, 22, 893 or it could be 56,58,12,1,22,893 I want to determine how many values are contained within the string [in this case only 6, but could be more] Simply, counting the commas and adding 1 one seems to be the easy answer? How can I create an Excel formula to count the commas, or please suggest an alternative method. Thanks for your help, Cheers, RonW Try this... =LEN(A1)-LEN(SUBSTITUTE(A1,",","")) Rick <Ron.Winkley@gmail.com> wrote in message news:6d78f2e8-60bd-4a88-9c3e-ddc107481cfc@34g2...

SUM IF problem
I have the following formula =SUM(IF(SCAR!$A$8:$A$9000=Sheet3!H$1,IF(SCAR!$H$8:$H$9000=Sheet3!$F44,1,0))) And it seemed to work pretty well so I dragged it into 4 additional columns and 74 additional rows (allowing the non $ protected variables to change) and it worked on a few but then returns zeros everywhere else. It should continue to count the occurrances of what is input in the "F and H" but it just seems like Excel gave up after 25 stores and left the other 49 hanging.... see example below.... it picked up at the end but I wonder about that too because the store number (fa...

problem about count text
Dear all, I have a table A B SD ASDFG SDFTY WREGD UJHDSD I want to count the no. of appearance of the text at B1 in column A, in this case, is 3. The formula is =countif(A2:A5, "*SD*"). However as the text "SD" would be changed all the time, I have tried the formula =countif(A2:A5, "*(B1)*"), but it fails. What should the formula please. Kent Try it this way: =3Dcountif(A2:A5, "*"&B1&"*") Hope this helps. Pete On Dec 8, 9:05=A0am, "Kent&q...

counting number of values
Hi, I am glad I have discovered this post, because I am new to excel and on a steep learning curve due to the requirements of my work. I have a large one-column list of values which includes duplicate values. i would like to calculate and display the number of times each value occurrs. for example. 1 2 4 4 6 The result should display something like 1=1, 2=1, 4=2, 6=1. Any help would be appreciated. Thanks. Mark =) Mark, Select your column of numbers - make sure that there is a header - and then choose Data / Pivot table and click OK. This will create a new sheet with a blank pivot ta...

how to filter and count contacts with email but no address
Trying to get a list of contacts that I need to call to get their address. How to do this in Outlook 2007. Also have Outlook 2003 on another PC. Is process different for that version? Would also like to find how to filter records with a value and without a value. e.g. who has an address and who doesn't have an address. Thanks. "MarkB" <nospam@nospam.com> wrote in message news:eIzGsNEzKHA.4752@TK2MSFTNGP04.phx.gbl... > Trying to get a list of contacts that I need to call to get their address. > How to do this in Outlook 2007. Also have Outlook...

counting a string of negative or positive numbers
These are daily performance figures. How would I calculate the mos consecutive losing days and related to that, the largest drawdow (cummulative performance of that streak of losing days). What about th 2nd and 3rd longest streaks and drawdowns? My real set of numbers i >1000, so eyeballing it on a chart doesn't work. I'm thinking I wil need to use the sumif command, but haven't gotten any farther tha that. Thanks for any help you can provide. Megan 0.32% 0.73% 0.61% 0.90% -0.23% 0.41% 0.00% 0.24% -0.07% 0.09% -0.32% 0.46% 0.06% -0.81% 0.53% 0.40 -- mcarringto --------------...

Selecting more than 30 cells for sum
Hello just wondering, to help out someone at work , he has an worksheet with over 30 cells all over the place that he wants to sum up some like B3+B5+B9 and so on but when he gets to 30 it errors on him, selecting too many cells, ( its like excel 97 or something) just wondering if there is another way of selecting mutilpe cells but with gaps inbetween.. cheers wayne this might be a little better =SUM(B2,C5,D3) or a macro assigned to a button Sub sumhigh() MsgBox Application.Sum(Selection) End Sub -- Don Guillett SalesAid Software donaldb@281.com "funkmaster" <waynesell...

Summing same sheet $A$1 in multiple workbooks
H E L P .... I have 26 different files that each have a 9 sheet workbook. I need to "summarize" all of the files (workbooks) into a consolidated workbook with the 9 worksheets. I know if you wanted to sum $A$1 in all of the worksheets, I can place all of the worksheets between two blank worksheets called A and one called Z and write the cell to sum(A:Z!$A$1) Is it possible to do the same with files??????? Thank Jerry Dear Jerry It is possible to reference external workbooks in your formula Using a sum function as an example your formula would look something like this... =...

pasting a hi-lighted auto sum
In excel, when you hi-light a column of numbers, the program automatically sums the hi-lighted numbers. My question is this, is there a way to copy this auto sum total to another speadsheet that I have open? Thanks for your help! Mark I assume you mean the SUM down on the Status Bar. AFAIK there is no way to copy this number. Workaround........select the cells then hit the AutoSum button on your Standard Toolbar. Copy and Paste Special>Values to the other workbook or sheet. Gord Dibben Excel MVP On Wed, 9 Feb 2005 07:59:06 -0800, "MarkT" <MarkT@discussions.microsoft...

Count Field Contents
Hello, I have four fields call Team1, Team2, Team3 & Team4. Each of the fields are linked to a lookup table that has 22 team names. The database consist of people that can be a part of up to four team. I wish to create a report that list all 22 teams and the amount of people in each team. I have no problem creating a report that groups by one team field, however, my problem is how do I do it with the four team fields. Thanks for any help you can provide. ...

Sum multiple VLOOKUP values
I have a table with 2 columns: S 9 M 3 W 1 N 0 I have 9 rows with values of S, M, W or N. I want to add these values all together in one cell using VLOOKUP. Thoughts? Thank you Use SUMIF, why would you want to use VLOOKUP? -- Regards, Peo Sjoblom "AFuturePrez" <jmtrostle@gmail.com> wrote in message news:0ba58c57-318e-4448-89ac-1d31eef7bea8@2g2000hsn.googlegroups.com... >I have a table with 2 columns: > S 9 > M 3 > W 1 > N 0 > > > I have 9 rows with values of S, M, W or N. I want to add these values > all toge...

Summing Text & Numbers
I need to sum a range of cells that will contain both text and numbers. The text will always be an "s" and the number will be a number between 0.25 and 10, in multiples of 0.25. The entry in the cells to be counted will be entered as "s.25" or"s.5.50", for example, and the number following the "s." needs to be totaled. There will be some blank cells in the range. Thanks I know it sounds Ridiculous but at this time of the day and assuming the range of values is A1:A20 - try this: {=SUM(VALUE(RIGHT(A1:A20,LEN(A1:A20)-(LEN(A1:A20)-LEN(S...

Multiple Criteria Sumif/Sum..tried & failed Ctrl+Shift+Enter,
Strange problem this...I have followed to the letter differnt methods of solving this: 3 Columns A, B and C Column A is in date format DD/MM/YYYY at the moment 17/06/2005 Column B is picked from a list but for now contains just text (i.e- name of saleman say "Bob") Column C is currency format and for now just contains just one entry ("�55.00") What i want is to sum all sales from A for that date if the saleman is Bob And this is what i did...... =SUM((B1:B11="bob")*(A1:A11="17/06/2005")*C1:C11) Remembering of course to press Ctrl+Shift+Enter to...

Sum Range
Hi, is there a way to limit a running sum to the previous 12 or so records? For example, in Excel I can use =sum(a1:a12) in cell b12. Then copy down that formula into cells b13 thru b24 automatically changing the 1 and 12 to 2 and 13, 3 and 14, 4 and 15 and so on always summing the last 12 values. Thanks, Mike No and Yes. Access stores record in a big bucket. When you view a set of records, the record number is for that display but is not related to the record itself. With a different sort or criteria the records will indicate a different record number. You must have something...

How do I sum a number of columns and ignore the minimum value?
=SUM(A:G,-MIN(A:G)) However, this only removes the min. value once. If the minimum value appears several times and you want to ignore all instances of it, then use: =SUM(A:G,-SUMIF(A:G,MIN(A:G))) HTH Jason Atlanta, GA >-----Original Message----- > >. > Nicole =SUM(A1:M1)- MIN(A1:M1) If you have ties for MIN, only one will get dropped. =SUM(IF(A1:M1<>MIN(A1:M1),A1:M1)) entered with CRTL + SHIFT + ENTER will SUM only cells that are greater than MIN(drop all MIN's) Gord Dibben Excel MVP On Thu, 9 Dec 2004 16:41:06 -0800, Nicole <Nicole@discussions.micros...

Counting Occurances (2003)
I have a cell that contains.... "2 red hat, 1 green glove, 2 red glove, 2 yellow hat" What I need to be able to do is; count the number of items containing "2 red" and return the value 4. And; count the number of items containing "2 yellow" and return the value 2. I've tried using COUNTIF, FIND, SEARCH, etc. and can't come up with a way to do it. Thank you in advance for your help! hi can you please put int in grid format so that i can figure it out what you want? "PeterM" wrote: > I have a cell that contains...

subract from sum?
How can I sum a column but excluded certain figures based on another cell Hi =SUM(B1:B100)-SUMIF(A1:A100,"X",B1:B100) or =SUMIF(A1:A100,"<>X",B1:B100) -- Regards Frank Kabel Frankfurt, Germany "starrpro" <starrpro@discussions.microsoft.com> schrieb im Newsbeitrag news:0EA544DC-37DD-4197-9344-2BFDFCC0DB66@microsoft.com... > How can I sum a column but excluded certain figures based on another cell ...

Question on Printing Subtotals
Probably answered dozens of times, and I did a Google search with no success. I have subtotals calculated in three fields, Puchase Price, Paid, and Balance. I hide the details to Copy - Paste only the calculated subtotals to another workbook, but all the details are pasted as well. Is there a way to suppress Pasting the details. I could obviousy accomplish this with a Pivot Table, but management prefers the display provided by Subtotals. Many thanks. use a macro for specialcells(xlvisible) or use f5 goto special>visible cells only -- Don Guillett SalesAid Software donaldb@281.com &...

Summing rows based on criteria in another column
I'm trying to consolidate rows of data based on the ID and OP column. In the attached image, I would like to consolidate ID # 190 because OP column is blank or zero and leave the other rows of data as is if there is a value in the OP column. +-------------------------------------------------------------------+ |Filename: excel problem.gif | |Download: http://www.excelforum.com/attachment.php?postid=4732 | +-------------------------------------------------------------------+ -- reybie -----------------------------------------------------------...

How can I count unique values in a query in the report footer
Am using Access 2003 I have a report that gives me the count of the status of individuals . This works fine as long is there is only one record in the query (in my query there is one record per month). When I query 12 months (individuals may appear in various months) it counts each record of an individual. For Example in a query considering 12 months for a widow Jones it may count her 12 times and for a survivor named Smith may count her 8 times: Widows 12 Survivors 8 I would like to add a count in the report footer that will tell me how many unique individuals I have in the repo...

Counting based on a formula result
Hi, I wanted to count cells based on the result of the formula, "Type(A1)" I wanted to accomplish this in a single formula. I have used sumproduct to count, for example to count the number of occurances of the word "Car" in a given range: =Sumproduct((A1:A100=Car)*1) Is there anyway to do this for the "Type(A1:A100)", or is there an array formula? Thanks Hi Jeff see your other post Cheers JulieD "Jeff" <Jeff@discussions.microsoft.com> wrote in message news:23337D82-0E52-43CB-B98D-73919ED9BB66@microsoft.com... > Hi, > > I wan...

SUM IF problem #2
Max, I wish it would have worked, but unfortunately it didn't. Thank for your suggestion! Max Wrote: > Not sure whether this would help with the recalc / recalc efficienc > issues, > but guess we could try the equivalent non-array SUMPRODUCT: > > =SUMPRODUCT((SCAR!$A$8:$A$9000=Sheet3!H$1)*(SCAR!$H$8:$H$9000=Sheet3!$F44)) > > -- > Rgds > Max > xl 97 > --- > Singapore, GMT+8 > xdemechanik > http://savefile.com/projects/236895 > -- > "ttmannan" ttmannan.1wteod@news.excelbanter.com wrote in message > news:ttmannan.1wteod@news.ex...

summing totals from subform on mainform
Hi there, I have looked through the posts to see if i could find some help on it but i still cant get things to work... I have a form called Billing Panel. I have a subform called BillingPanelTravelSubform The Billing Panel shows all the information for a work order The subform is linked by work order number There are for example 5 Travel invoices for one work order which means there are 5 records in the subform. I have a field on the subform called "T Total Travel" (which is an expression) and in the footer of the subform i have an unbound text box with the expression =su...

Credit Limit WarnTotal Balance and Sum of current and beyod exceed
Microsoft Dynamics Incident Number: 8625964 Incident Type: Technical Support Subject: Credit Limit on debtor card not working Occurs At: All Workstations Current Status: Open - Solution Delivered Not Confirmed Originated: 8/1/2006 4:44:00 PM GMT+08:00 Originated by: Touchstone Group Plc Authorized number: 4814417755 Regarding Customer: Trench (UK) Limited Team: EMEA Great Plains Support Region: Europe Agent: Caroline Nolasco Product Line: Microsoft Dynamics GP Topic: Receivables Management Version: 8.0 Application Language: English Database: Microsoft SQL2000 Server OS: Windows XP sp2 8/3/20...

Count No. of times Dates are repeated
Hi, I have a database where in dates are repeated in column D and names in column I. I want a formula in a different worksheet of the same file wherein I get the count of dates column along with the individual name. The intention is to get number of days each individual is present. The same dates could be repeated any number of times for the same individual. Example for the month of Dec. 2005 Dates Names 20 AD 15 BC 08 GH........etc Cheers, Mandeep Dhami Have you considered a Pivot Table? Data>Pivot Table Use Excel Select your data Click the [Layout] button ROW...