+ Reply to Thread
Results 1 to 5 of 5

Count cells with specific year and add up cells adjacent to the counted cells

  1. #1
    Registered User
    Join Date
    02-10-2015
    Location
    Brussels
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Count cells with specific year and add up cells adjacent to the counted cells

    Hello dear Excel experts,

    What formulas should I use in the attached file to create a table on sheet2 that looks like this:

    Column A: number of times that the year 2014 appears on the corresponding row in sheet 1. For instance, in sheet2 A2 would appear the number of cells from sheet1 row2 which include a date in year 2014. This would be 2 (K2 and S2).

    Column B: the sum of the adjacent cells to the left of the dates in year 2014. For instance, in sheet2 B2 would appear the sum of all the cells from sheet1 row2 just left of the ones in year 2014. In this case: 62,00 (J2+R2).

    Column C: same as column A, but for year 2013
    Column D: same as column B, but for year 2013
    Column E: same as column A, but for year 2012
    Column F: same as column B, but for year 2012
    etc. down to 2000.

    The table is very large with many rows and columns (I am only attaching a small part of it). The lengths of the rows are uneven. I should also mention that the dates and numbers are stored as text. I have no idea how to quickly convert all the dates without messing with the other data (if this has to be done at all). And I have not found a quick way to convert all the numbers without messing with the dates.

    I would forever be grateful if someone could help me with this. I am a beginner at Excel and have not found a solution on the internet. FYI, your reply will contribute to the work of a foundation that helps children.

    Thank you!

    PS: Sorry about my English, it's not my mother tongue.
    PPS: I will be offline for the next 15 hours, so please don't be mad at me if I don't reply immediately :-)
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Count cells with specific year and add up cells adjacent to the counted cells

    The following code will convert the text numbers to true numeric values and the text dates to true dates. The first part converts the decimal separator from "," to "." The second uses Text to Columns to convert the dates.

    Please Login or Register  to view this content.

    Regards, TMS
    Attached Files Attached Files
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Count cells with specific year and add up cells adjacent to the counted cells

    A1
    Please Login or Register  to view this content.
    B1
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson

  4. #4
    Registered User
    Join Date
    02-10-2015
    Location
    Brussels
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Re: Count cells with specific year and add up cells adjacent to the counted cells

    TMS and ProtonLeah, thank you so much for your replies!

    TMS, if I understand well, I should list in your code all the columns with numbers/dates? The complete table has a great number of columns so it would be difficult. Is there any other way I could do it? By the way I don't think the "," need to be replaced by ".", because when I convert a cell to number in my Excel, the decimal separator is still "," (the norm in my country - otherwise, my Excel is in English). I must also confess that I am a bit afraid of macros, so if there was a formula I could use instead it would be great. But maybe this is impossible?

    Thanks for your help, I really appreciate it.

  5. #5
    Registered User
    Join Date
    02-10-2015
    Location
    Brussels
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Re: Count cells with specific year and add up cells adjacent to the counted cells

    I have been able to convert all the numbers by clicking on the first cell with a "number stored as text" error, then Ctrl+Shift+End, then "convert to number" in the error drop-down menu. It has taken out the 0 in front of some codes in other cells, but that's not a problem for me. The dates are still stored as text, though. Could there be formulas that use the date cells as they are, for instance by looking for the text "*/2014", "*/2013", etc.?

+ 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] Count cells in column unless they've already been counted
    By JB33 in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 04-04-2014, 11:41 AM
  2. Replies: 4
    Last Post: 01-13-2014, 08:13 AM
  3. [SOLVED] Report counted cells based on day and month of the year
    By Chanley24 in forum Excel General
    Replies: 2
    Last Post: 05-08-2013, 09:17 PM
  4. Replies: 7
    Last Post: 12-04-2012, 06:23 AM
  5. Count cells with a specific year
    By Saturn in forum Excel General
    Replies: 4
    Last Post: 04-17-2011, 09:33 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