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