edit formula year() function to reference year in cell, not (NOW())

[MurphDog]

I've got a table on one sheet that displays payment amount from a amortization table on another sheet (sheet1). It matches the month and current year on the amortization table and displays in columns titled with each month (format mmm). This is the formula. It works perfectly as is

=SUMIFS(sheet1!$N$20:$N$379,sheet1!$B$20:$B$379,">="&1&B$4&YEAR(NOW()),sheet1!$B$20:$B$379,"<"&1+EOMONTH(--1&B$4&YEAR(NOW()),0))

But now I want to change it so that it displays a specific year. The year is in cell A1 in the form of yyyy .

How can I change the formula to reference the year in that specific cell? I've also considered referencing the year in my new column titles in the format of mmm-yy, but I'd much prefer it reference A1.

I thought I could change YEAR(NOW()) to YEAR ($A$1) where A1 = whatever year i enter or YEAR(U$6) to reference the column title, but both return incorrect values (0). Please advise

 

[MARK858]

Do you have an actual date in A1 or just the year? If it is just the year then reference $A$1 not YEAR($A$1)

 

[MurphDog]

Just the year. Thank you. That worked!

 

[MARK858]

Your welcome.

Just remember a date is only a number formatted to look like a date, specifically the number is the number of days starting from 1st January 1900.
So if you put this year in A1 then it would be the 2020th day (off by 1 because Excel thinks 1900 was a leap year) which if you formatted the cell as a date you would get the twelfth of July 1905, which means Year(A1) would return 1905 not 2020.