+ Reply to Thread
Results 1 to 4 of 4

How to sum data by month, by quarter, by year?

  1. #1
    Registered User
    Join Date
    04-15-2016
    Location
    Chico, CA
    MS-Off Ver
    2011
    Posts
    14

    How to sum data by month, by quarter, by year?

    I have column B with pricing data and column C stating the billing plan.
    The billing plans are either monthly, quarterly, or yearly.

    What equations should I use to sum these based on the billing plan?

    For the billing plan column, should I use numbers instead of words? (12 for yearly, 4 for quarterly, 1 for month)

    Example:

    A B C
    January 2016 Price Plan
    $10 Monthly
    $40 Quarterly
    $120 Yearly
    February 2016
    $10 Monthly

    January 2016 Sum =$170
    February 2016 Sum =$20
    March 2016 Sum =$20
    April 2016 Sum =$20
    May 2016 Sum =$60
    ...
    January 2017 Sum =$180

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,503

    Re: How to sum data by month, by quarter, by year?

    If you normalize your data with the following layout then you can use the Sumifs function

    v A B C
    1 Month/Year Amount Plan
    2 Jan-16 10 Monthly
    3 Jan-16 40 Quarterly
    4 Jan-16 120 Yearly
    5 Feb-16 10 Monthly

  3. #3
    Registered User
    Join Date
    04-15-2016
    Location
    Chico, CA
    MS-Off Ver
    2011
    Posts
    14

    Re: How to sum data by month, by quarter, by year?

    How do I use SUMIF for multiple criteria?

    How do I make the equation sum all of January's data in the month of January but ignore row 3 and row 4 when summing February?

    I'm thinking I can make a dynamic column D based off of "Plan" column where column D will go from active to inactive based on time? (Don't know if this is necessary and don't know how to do)
    Last edited by kevinSHR; 05-06-2016 at 03:52 PM.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,503

    Re: How to sum data by month, by quarter, by year?

    Here is a link for = SumIfs

    Note the s in the formula

    http://www.techonthenet.com/excel/formulas/sumifs.php

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Month/quarter/semestre/year calculations
    By pccamara in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-05-2016, 08:37 AM
  2. Replies: 4
    Last Post: 08-05-2015, 06:39 PM
  3. [SOLVED] Using SumProduct for dates inclusive of Year to Date, Month to date, Quarter to Date
    By cartica in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2014, 04:22 PM
  4. Expanding 4th quarter by month ONLY for certain year
    By Melvinrobb in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-28-2013, 06:13 PM
  5. [SOLVED] PIVOT TABLES: Grouping dates by Fiscal Year, Quarter and Month
    By bimmer5dude in forum Excel General
    Replies: 5
    Last Post: 06-30-2012, 09:13 PM
  6. Replies: 9
    Last Post: 11-16-2009, 07:16 PM
  7. [SOLVED] maturity schedule fixed inc securities by month, quarter and year
    By maturity schedule in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-31-2006, 07:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1