CODEWITHSUNDEEP
excelformula
Mark harris
Mark harris

Reputation: 543

Sum all number values in one column where year of date in another column equals 2014?

I am using the following formula to sum all the number values in one column where the year of a date is 2014 in my other column.

Here's what I have so far but it isn't working and returns a zero value:

=SUMIF('Completed Tenders'!B:B,YEAR(2014),'Completed Tenders'!K:K)

Please can someone show me where I am going wrong?

Upvotes: 0

Views: 67

Answers (1)

Robin Gertenbach
Robin Gertenbach

Reputation: 10816

Your condition is YEAR(2014) which is the year for 2014 days from the first of Jan 1900. You're basically saying you want to sum all tenders from July sixth 1905.

Try this which will check for the datevalue being greater or equal to the start of the year and smaller or equal to the last day of the year:

=SUMIFS('Completed Tenders'!K:K,'Completed Tenders'!B:B, ">="&DATEVALUE("2014-01-01"), 'Completed Tenders'!B:B, "<="& DATEVALUE("2014-12-31"))

Upvotes: 2

Related Questions