find a year in a string of dates

[Carrie_Loos via OfficeKB.com]

I have a column of dates that span across several years in column C (1/2/2009)
format and I have hours to complete the project in column Q. I want to make a
cell, A21, where the user can input a year (2009) and pull all the data for
that year which is the first part of the formula below but I can't seem to
get it to recognize just that year. I have tried a combination of different
things but without success. Can someone see the error of my ways??

=IF('Hours Worksheet'!$C$4:$C$1464=$A$21,SUMIF('Hours Worksheet'!$D$4:$D$1464,
26,'Hours Worksheet'!$Q$4:$Q$1464),0))

Thanks

 

[T. Valko]

Try this:

=SUMPRODUCT(--(YEAR('Hours Worksheet'!$C$4:$C$1464)=$A$21),--('Hours
Worksheet'!$D$4:$D$1464=26),'Hours Worksheet'!$Q$4:$Q$1464)

 

[Carrie_Loos via OfficeKB.com]

It works, Thanks! I guess I need to practice with the SUMPRODUCT formula some
more...

Try this:

=SUMPRODUCT(--(YEAR('Hours Worksheet'!$C$4:$C$1464)=$A$21),--('Hours
Worksheet'!$D$4:$D$1464=26),'Hours Worksheet'!$Q$4:$Q$1464)

I have a column of dates that span across several years in column C
(1/2/2009)

[quoted text clipped - 12 lines]

Thanks

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

It works, Thanks! I guess I need to practice with the SUMPRODUCT formula
some
more...

Try this:

=SUMPRODUCT(--(YEAR('Hours Worksheet'!$C$4:$C$1464)=$A$21),--('Hours
Worksheet'!$D$4:$D$1464=26),'Hours Worksheet'!$Q$4:$Q$1464)

I have a column of dates that span across several years in column C
(1/2/2009)

[quoted text clipped - 12 lines]

Thanks