+ Reply to Thread
Results 1 to 7 of 7

Sum all figures for a given month

  1. #1
    Registered User
    Join Date
    06-17-2020
    Location
    Hull
    MS-Off Ver
    365
    Posts
    3

    Sum all figures for a given month

    Hi all

    I'm stuck with a formula and I wonder if anyone can help me.

    I have a spreadsheet with 365 columns, one for each day of the year. What I would like to do is sum totals in a row which occur in a particular month.

    So for example

    Row A 29/1/20, 30/1/20, 31/1/20, 1/2/20
    Row B 2 , 3 , 3 , 2

    What I would like to do is sum row B by month.

    So in this case would like the sum of January which equals 8.

    I have tried hlookup but failed miserably


    Cheers
    Phil

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: HLOOKUP help

    HLOOKUP() only retrieves a single datum from a table. SUMIFS() is the function that will sum one range based on criteria from another range https://support.microsoft.com/en-us/...rs=en-us&ad=us =SUMIFS(reference to row B,reference to row A,">="&DATE(2020,1,1),reference to row A,"<="&DATE(2020,1,31)).

    If this is more of a database type thing -- where you would have a good database of dates and values -- it could be preferable in the long run to arrange the data in a good database, then use tools like pivot tables to summarize the data. https://www.excel-easy.com/data-****...ot-tables.html
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: HLOOKUP help

    Welcome to the Forum, Phil.

    HLOOKUP will only return a single value, so you can't use it to sum values.

    You could use SUMIF if you had another row which included the month, or you could use SUMPRODUCT.

    It would help if you attached a sample Excel workbook - the procedure for doing this is given in the yellow banner at the top of the page.

    Hope this helps.

    Pete

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: HLOOKUP help

    Welcome to the Forum grazey!

    HLOOKUP will not give you what you want, which is why we encourage people to describe their problem rather than what they think the solution is.

    First, your dates should be Excel dates, so hopefully that's what your data is in row 1 (A is a column, not a row).

    Where in your data does it indicate that you want the sum for January? Suppose you have 1/1/2020 in cell C1. The formula to sum row 2 for January would be

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If this does not help solve your problem please see yellow banner at the top of the page and attach your file.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  5. #5
    Registered User
    Join Date
    06-17-2020
    Location
    Hull
    MS-Off Ver
    365
    Posts
    3

    Re: Sum all figures for a given month

    Hi there, firstly thanks for the speedy friendly answers.

    I've had to wait until I got home from work as our firewall does not allow uploads.

    Ok here's the spreadsheet. I need the formula to go in BH2. I would like it to sum all row 2 values which occur in February.

    THanks very much!

    Cheers
    Phil
    Attached Files Attached Files

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Sum all figures for a given month

    The most flexible way to do this is to put the month you are interested in in a cell, rather than baking it into a formula. That makes it easier to change later. I put 1/2/2020 in BH1 to represent the month you want, and it is displayed as Feb 2020. To use a different month, type in the desired month, using the first day of the month.

    MrShorty's second suggestion is a good one if your data gets much more complicated than this.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-17-2020
    Location
    Hull
    MS-Off Ver
    365
    Posts
    3

    Re: Sum all figures for a given month

    Quote Originally Posted by 6StringJazzer View Post
    The most flexible way to do this is to put the month you are interested in in a cell, rather than baking it into a formula. That makes it easier to change later. I put 1/2/2020 in BH1 to represent the month you want, and it is displayed as Feb 2020. To use a different month, type in the desired month, using the first day of the month.

    MrShorty's second suggestion is a good one if your data gets much more complicated than this.
    Great, that's perfect! thanks so much.

    Phil

+ 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. Getting month to date and run-rate figures
    By excelab in forum Excel General
    Replies: 6
    Last Post: 02-06-2020, 09:02 AM
  2. Replies: 1
    Last Post: 03-14-2019, 08:42 AM
  3. Get the average figures per month
    By hep in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 06-15-2013, 04:15 PM
  4. Conversion of an array of figures inputted into cells as word figures to Excel figures
    By Allan Simpson730 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-08-2013, 07:25 AM
  5. Replies: 4
    Last Post: 03-19-2012, 08:58 AM
  6. Choose YTD and Month figures
    By terregles in forum Excel General
    Replies: 4
    Last Post: 03-10-2009, 11:42 AM
  7. How to automatically tabulate month end figures.
    By KNicholas2 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 12-06-2007, 05:35 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