+ 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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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