• 412K Members
• 3,916 Online
• 468K Conversations
SOLVED

New Contributor

# finding the year in a range of cells

Hello everybody,

I'm trying to find a way that I can work out the year for every cell in a column, without having to individually type "YEAR (a1), Year (a2)….etc".  The column could hold 65000 cells eg. a1-a65000.

each cell in the range will have a different date and year. on each Row there is also a dollar value associated with that month and year. Just to make things harder, the dates on each row will not necessarily be in order.

I need to make a table that finds the year and the dollar value and the month. Each row in the table is for another year eg 2019, 2020, 2021 etc. and each column in the table is for the month. All dollar  values for that year, in that month needs to added together, to give a final total of sales for that month and year.

I am absolutely hopeless with VBA, and only just above a novice in excel use. Any help anyone could give me would be really helpful and appreciated

Jeff

4 Replies
Solution

# Re: finding the year in a range of cells

Hi Jeff,

Few variants

- you may pivot your source by year and month added in data model;

- you may use formula as SUMPRODUCT

- you may use Power Query

perhaps something else

Sample is attached.

# Re: finding the year in a range of cells

Hello Sergei,

That is exactly what I am looking for. Thank you so much, I have been struggling with this for months, and in one night you showed me a nice simple way of doing this.

Thank you

Regards

Jeff

# Re: finding the year in a range of cells

Hi Sergei,

I hate to be a pain, however it looks like I have transcribed the formula you gave me, incorrectly.

The problem is I have 3 sheets in the excel file which have the dates and cost on sheet 1 (Sales), sheet 2 (Payments) is only for when someone pays us, and sheet 3 (Yearly Figures) is where I am trying to keep a constant total of each month in a certain year.

This is the formula that I used: =SUMPRODUCT((YEAR('Sales '!A18:'Sales '!A18)=\$C6)*(MONTH('Sales '!A18:'Sales '!A18)=D\$5)*'Sales '!I18:'Sales '!I18)

I've included some snapshots of each page.

Best Regards

Jeff

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies