Forum Discussion

Copper Contributor

Feb 27, 2019

Solved

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 ce...

excel

office 365

SergeiBaklan
Feb 27, 2019
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.
Year Month combine money.xlsx121 KB

SergeiBaklan

Diamond Contributor

Feb 27, 2019

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.

Year Month combine money.xlsx121 KB

Jreggy

Copper Contributor

Feb 28, 2019

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

SergeiBaklan
Diamond Contributor
Feb 28, 2019
Jeff, glad to help
- Jreggy
  Copper Contributor
  Feb 28, 2019
  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.
  
  May I ask for your help once again
  
  Best Regards
  
  Jeff