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

1. ## 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 :-)

2. ## 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

3. ## 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.``

4. ## 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. ## 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.?

##### Users Browsing this Thread

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

#### 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