Subtotals by Year?

Hi - here's the scenario:

Column A is a person's name.
Column B is a date, e.g., 4/1/1979.
Column C is an amount of money.

I'd like to subtotal this spreadsheet by date - but not by individual
day, rather by year - i.e. I would like one subtotal for 1979, another
for 1980, etc.

Is this achievable?

Thanks very much.
0
1/6/2009 4:39:10 PM
excel 39879 articles. 2 followers. Follow

4 Replies
390 Views

Similar Articles

[PageSpeed] 27

try
=sumproduct((a2:a22="joe")*(year(b2:b22)=1979)*c2:c22)

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Guest3731" <notconfusedaboutthattoday@gmail.com> wrote in message 
news:4f97de2b-fde7-4548-98bc-a179af5996ce@v31g2000vbb.googlegroups.com...
> Hi - here's the scenario:
>
> Column A is a person's name.
> Column B is a date, e.g., 4/1/1979.
> Column C is an amount of money.
>
> I'd like to subtotal this spreadsheet by date - but not by individual
> day, rather by year - i.e. I would like one subtotal for 1979, another
> for 1980, etc.
>
> Is this achievable?
>
> Thanks very much. 

0
dguillett1 (2487)
1/6/2009 4:50:29 PM
Consider a Pivot Table...

  http://www.contextures.com/xlPivot07.html


In article 
<4f97de2b-fde7-4548-98bc-a179af5996ce@v31g2000vbb.googlegroups.com>,
 Guest3731 <notconfusedaboutthattoday@gmail.com> wrote:

> Hi - here's the scenario:
> 
> Column A is a person's name.
> Column B is a date, e.g., 4/1/1979.
> Column C is an amount of money.
> 
> I'd like to subtotal this spreadsheet by date - but not by individual
> day, rather by year - i.e. I would like one subtotal for 1979, another
> for 1980, etc.
> 
> Is this achievable?
> 
> Thanks very much.
0
jemcgimpsey (6723)
1/6/2009 5:34:05 PM
On Jan 6, 11:34=A0am, JE McGimpsey <jemcgimp...@mvps.org> wrote:
> Consider a Pivot Table...
>
> =A0http://www.contextures.com/xlPivot07.html


Thanks very much for both of these knowledgeable suggestions...
0
1/6/2009 6:33:05 PM
Hi,

And another variation

=SUMPRODUCT(--(A$1:A$100=D1),--(YEAR(B$1:B$100)=E1),C$1:C$100)

where you enter the persons name in D1 and the year in E1.  The advantage in 
using cell references for the D1 & E1 is that you can set up a table rather 
quickly and just copy the formula down.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"Guest3731" <notconfusedaboutthattoday@gmail.com> wrote in message 
news:4f97de2b-fde7-4548-98bc-a179af5996ce@v31g2000vbb.googlegroups.com...
> Hi - here's the scenario:
>
> Column A is a person's name.
> Column B is a date, e.g., 4/1/1979.
> Column C is an amount of money.
>
> I'd like to subtotal this spreadsheet by date - but not by individual
> day, rather by year - i.e. I would like one subtotal for 1979, another
> for 1980, etc.
>
> Is this achievable?
>
> Thanks very much. 

0
1/6/2009 11:31:51 PM
Reply:

Similar Artilces:

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 case. ---------------- 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..
Hi everyone, 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" MM/DD/YYYY). 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 please? Thanks Arpa. =SUMPRODUCT(--...

Default year in date entry
(Excel 2003) 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 http://OakRoadSystems.com/ "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/ Stan, 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 for this? Thanks! How about using a from date and to date in a table such that you can have multiple ranges per person. tblProject: ProjectId tblPerson: PersonId tblAssignment: ProjectId PersonId FromDate ToDate -- Dorian "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 sense. 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 possible? 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 problems/solutions. 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 =IF(MONTH(A1)<>MONTH(A1+1),B1,"") 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: http://peltiertech.com/WordPress/2008/05/19/clustered-stacked-column-charts/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Miles04" <Miles04@discussions.microsoft.com> wrote in message news:41C2A5A4-1304-4F58-8DED-646949878790@microsoft.com... > 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: Format([SHIP_DATE],"q") is there another letter/s to use in place of "q" for half years Thanks Matt 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: > > Format([SHIP_DATE],"q") > > is there another letter/s to use in place of "q" for half years > > Thanks > Matt 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. -- Developer When you going to close year it will close 2006 first as per your case becasue both year are opened. Regards, Sheikh Asif 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. Frank Hamelly MCP-GP, MCT East Coast Dynamics...

Listing months and years in two columns based on start and end date.
Hello Everyone, 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 reached. Is there a way to do this? Thanks! KLyn -- 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 =SUMPRODUCT(--(ISNUMBER(FIND("Total",A1:A300))))-1 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 =SUM(1/COUNTIF(A2:A300,A2:A300)) entered with ctrl + shift & enter Regards, Peo Sjoblom "Karen S." wrote: > I subtotal my current balance on a...

How to caculate subtotal of every other lines
for example 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 Hi for column A try =SUMPRODUCT(--(MOD(ROW(A1:A100),2)=1),A1:A100) (for your 'a, c, e' rows) and =SUMPRODUCT(--(MOD(ROW(A1:A100),2)=0),A1:A100) for the 'b, d, f' rows -- Regards Frank Kabel Frankfurt, Germany > 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 Money? 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 >Money? > >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 updates. 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 https://billpay.msn.com page. The subject should have been. After install of Money 2004 Premium 2 Years of BillPay not available. "R...

leap year question
Hi, 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 Thalia use a formula in A2 of =A1+(YEAR(A1+1)=YEAR(A1) and copy down to A366 or even just put =IF(YEAR...

How do you extract day/time/month/year information
Hello, 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. Try this: =MID(A1,FIND(" ",A1,FIND(":",A1,FIND(":",A1)+1))+1,20) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "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. thank you, John ...

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....... =AVERAGE(IF(TEXT(Work!$Q$3:$Q$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),IF(Work!$R$3:$R$4293<>"",Work!$C$2:$C$4293))) 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,"mmyyyy")),--(Work!$R$3:$R$4293<>""),Work!$C$2:$C$4293)/ 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? Much appreciated! Excel 2010 has this feature -- % of parent. You'll have to wait! :-( "Little Mojo" <david.j.snyder@united.com> wrote in message news:06B1C68C-868C-4E6C-A6A8-B705358E4CB8@microsoft.com... > 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 11/14/2007. The tax tables are downloaded from the menu. -- Charles Allen, MVP "MES" wrote: > 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...