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/
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 value...Ability to suppress subtotals in FRx Drilldown Viewer
Customer would like the ability to suppress subtotals in the transaction
detail level of the Drilldown Viewer. If there is only one line of detail,
you end up having two lines (one for the detail and one for the subtotal)
with the same number. Customer does not want to see the subtotal in this
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Micr...Cost changes at year end
We notice that we have transactions still with old unit costs, after we have
updated item costs as part of year end. What is the correct way to have
these tranasctions update unit cost to the new cost? example - we create
invoice when unit cost was 10.00 then at year end we recost the item to
12.00. we post the item after year end and the invoice still shows 10.00.
Our receivables account picks up the 2.00 difference.
...Counting nubmer of months with correct year..
I have a range of cells A1:A10, lets say, that contain
delivery date for some product. These date can vary from
say January to December of 2004 to 2008. The format is"
I like to write a formula that counts how many delivery
dates is scheduled this month for this year.
So the idea is that in Cell B1, I will type today's date.
Then I like the formula to tell me how many delivery is
scheduled (from the range A1:A10) that has this month and
this year typed-in.
I tried several things but without success. Any help
=SUMPRODUCT(--...Default year in date entry
When I enter e.g. 12/30 in a cell, Excel interprets that as the date
2007-12-30, as expected. But when I enter 1/6, Excel interprets that
as last January, not next week.
Is there any way to tell Excel that dates before a certain date in
the year should be interpreted as next year, not this year?
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/
Entering 1...Track project FTE per year for five years in Access?
I need to track individuals' FTE on projects for each year of the projects,
which could be up to 5 years. The FTE could be for three different time
periods within each year (e.g., summer, school year, calendar year for Year
1, Year 2, and so on). What are the best fields to create and table structure
How about using a from date and to date in a table such that you can have
multiple ranges per person.
"Give someone a fish and they ea...Check Dates in Payroll for year with End Year Wage file already c.
After the year end wage file has been created you cannot void a check within
that year, but you can create a check within that period. This makes no
You shouldn't be able to do a void or a check in a year in which the PR year
end wage file is created.
...Date picker scrollbar month and year only
Using 2003, I have a form (frmMSW) users input data for monthly solid waste
tonnage by location. Since data is collected monthly, wanted the user to be
able to select Month/Year period covered from a calendar scrollbar. Is it
FYI - using date picker scrollbar now. Users complaining they don't
understand what "Period Covered" means when the entire month calendar pops up.
Thank you guys for all the support. I've learned much from reading other
I created a small userform with a commandbutton, two labels and a scrollbar.
This...Month and Year
Is there a way to have Exel only pick out a number from a column if th
corresponding cell in another column is the end of the month or year
For example in column A, I have the "Date" in "1/2/1984" format. I
column B, I have "Account Balance." Is it possible in column C to sho
the Account Balance only if it is the end of the month or end of th
year in column A?
Thanks in advance
Message posted from http://www.ExcelForum.com
End of month
you won't need to test end of year, as end of year is also end ...100% stacked column with multiple years (3) and areas (7)
looking how to do a stacked column chart with multiple yeras and regions with
the years being group together under respected region
You could probably adapt this technique:
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
"Miles04" <Miles04@discussions.microsoft.com> wrote in message
> looking how to do a stacked column chart with multiple y...display data as a percentage of a subtotal in excel pivot table
how can data be displayed as a percentage of a subtotal in a pivot table?
for example, I can display salesperson A's 1st quarter sales as a percentage
of yearly sales. What I want to do is display salesperson A's 1st quarter
sales as a percentage of quarter 1 total sales.
You can add columns to your data table, and calculate the quarter, and
the percent of quarter total for each row. For example, with your data
in cells A1:C200 --
Salesperson Date Sales
Sam 1-Jan-05 200
In cell D1, type: Qtr
In cell D2, type: =CHOOSE(MONTH(B2),1,1,1,2,2,2,3,3,3,4,4,4)
In ce...Half years in a query
I am making a query and need to split it into half years. When I split
it into quarters i use:
is there another letter/s to use in place of "q" for half years
On Mar 31, 12:48 pm, msmuz...@gmail.com wrote:
> I am making a query and need to split it into half years. When I split
> it into quarters i use:
> is there another letter/s to use in place of "q" for half years
I don't think so, but you can probably use this is a calculated fie...Year End Closing for two open years 2006 and 2007 in one company
I have a situation with client that year 2006 and year 2007 are open and I
need to perform the year-end-closing process and I know this process for one
open year only and for this case how should I strart year by year.
Any help will be greatly appreciated.
When you going to close year it will close 2006 first as per your case
becasue both year are opened.
Tools>Routines>Financial>Year End Closing.
The system will force you to close 2006 before you can close 2007.
Years are closed one at a time.
East Coast Dynamics...Listing months and years in two columns based on start and end date.
I freely admit to being an Excel idjut and I hope you all can help m
out. What I would like to do is enter a start date in one cell and en
date in another cell. Then I would like for the spreadsheet to lis
the months in between the start date and end date in one column and th
associated year to that month in the next column.
For example, if my start date is May 2000 and my end date is July 2001
the columns should be May 2000, June 2000, July 2000 until the end dat
Is there a way to do this?
Message posted from http://www.ExcelForum.com
I fo...When I subtotal in Excel how can I count the number of subtotals?
I subtotal my current balance on address and then I want to count how many
distinct addresses there are
If you total for every change in address then each distinct address would be
the same as each Total? So
the minus 1 at the end is for the gran total that shoudn't be counted, or
just copy all the addresses somewhere else and use a formula on thos addresses
entered with ctrl + shift & enter
"Karen S." wrote:
> I subtotal my current balance on a...How to caculate subtotal of every other lines
T, t1, T2, T3, T4
A, 3, 4 , 2, 4
b, 3, 1, 3, 5
C, 3, 1, 3, 3
d, 3, 4, 5, 1
E, 3, 3, 3, 2
f, 3, 1, 3, 1
I want to get the subtotal of A,C,E rows and b,d,f row
Message posted from http://www.ExcelForum.com
for column A try
(for your 'a, c, e' rows)
for the 'b, d, f' rows
> for example
> T, t1, T2, T3, T4
> A, 3, 4 , 2, 4
> b, ...Offline version of MS Money this year?
It was announced recently that this fall MS will release an offline version
Are there any news regarding this? I don't need only features so I'm waiting
for this new release very much.
In microsoft.public.money, bullka wrote:
>It was announced recently that this fall MS will release an offline version
>Are there any news regarding this? I don't need only features so I'm waiting
>for this new release very much.
Check http://www.microsoft.com/money/faq.mspx for information
No update yet in that page :(
"Cal Learner-...After install of Money 2005 Premium 2 Years of BillPay not availab
I just finished install Money 2004 Premium and decided to take advantage of
the 2 years of free Bill Pay service through MSN. I selected to the
Services->Service Center menu and clicked on “getting started” under “Two
years free from MSN Bill Pay” and it offered only 3 months of service. It
seems as if the link in Money does not work properly.
How can I sign up for the service?
It seems as someone forgot about the offer when they updated the
The subject should have been.
After install of Money 2004 Premium 2 Years of BillPay not available.
"R...leap year question
I have a column for dates, starting with the first day of the year. I only
have to input the first day on cell A@ (say 01/01/06) and the all the days of
the years will be display in the same column (I wrote the simple formula
=A2+1). Now the next leap year is 2008 and i don't want to have to copy the
last row to add the extra day, I want excel to display the last row of the
date column if it's a leap year and not to display it when is not. What
should I do? Thank you
use a formula in A2 of
and copy down to A366
or even just put =IF(YEAR...How do you extract day/time/month/year information
I came across this issue, where I have a exported excel file. With the
information in a field "Friday 02:15:00 March 2009". I need these
information extracted. I only require Month/Year. I need to end up with a
field with only "March 2009". Is there a formula for this?
Thanks for your help.
Microsoft Excel MVP
"SV" <SV@discussions.microsoft.com> wrote in messag...Subtotal Line functions
I have used the TOOLS --> SUBTOTALS feature to display a TOTALS subtotal
line in sales data (total qty, extended cost, extended sale price, etc).
However, I wish to display a function which is a combination of the other
totals on the same line.
TOTAL_MARGIN = (SELL - COST) / SELL * 100
I don't want this TOTAL_MARGIN to be displayed on each line-item - only on
the TOTALS line.
I would like to create a template, to automate the process of creating thie
TOTALS line with the TOTAL_MARGIN value for non-technical users.
...Tricky Scenario finding days/months/years! #2
I have tried both your solutions, i return a #VALUE! Error when place
in both scenarios.
Here is the method in which i set up the data.
SMS SME Date Result
Mon 29/12/2003 Sun 25/01/2004 01/03/04 =VLOOKUP((G5),2,2,(E5:F16))
Mon 26/01/2004 Sun 29/02/2004
Mon 01/03/2004 Sun 28/03/2004 =TEXT(G5,E5:F16)
Mon 29/03/2004 Sun 25/04/2004
Mon 26/04/2004 Sun 30/05/2004
Mon 31/05/2004 Sun 27/06/2004
Mon 28/06/2004 Sun 25/07/2004
Mon 26/07/2004 Sun 29/08/2004
Mon 30/08/2004 Sun 26/09/2004
Mon 27/09/2004 Sun 31/10/2004
Mon 01/11/2...Average in a month/year
I am trying to get the average of a set of numbers if they occur in a given
month. I get the infamous #N/A. As an array.......
I would also like to count the number. I would assume I could swap out
average for count.
SUMPRODUCT(--(TEXT(Work!$Q$3:$Q$4293,"mmyyyy")=TEXT(A6,"mmy...Pivot Table Values as a % of a Subtotal
Hi! I have a pivot table that is summarizing sales data by "facility type"
and then "product category". I want to present the data with each value
shown as a % of the relevant subtotal (depending on which facility type and
product category it happens to be in)...not of the column as a whole. Is
this possible? If so, how?
Excel 2010 has this feature -- % of parent. You'll have to wait! :-(
"Little Mojo" <firstname.lastname@example.org> wrote in message
> Hi! ...9.0 SP3 and 2007 year-end tax update
Our client installed SP3 which is suppose to have the 2007 tax year-end
however the date of the last tax year-end update still shows last year. Is
there another step or are they ok if SP3 was installed successfully?
If you go to tools --> setup --> payroll --> payroll the date should be
The tax tables are downloaded from the menu.
Charles Allen, MVP
> Our client installed SP3 which is suppose to have the 2007 tax year-end
> however the date of the last tax year-end update still shows last year. Is
> there another step or are...