SUMIFS - using the year function as part of an array

[dhsilv2]

I'm trying to us both sumifs and countifs to gather various information on a set of loans. I'm wanting to look at the year the loan was originated as one of my criteria in these statements, but the dates are stored with the actual date.

I find that sumifs and countifs are generally MUCH faster built in functions in excel than sumproduct or building a sum if array.

{=SUMPRODUCT(--(IFERROR(YEAR(U:U),0)=2014),AV:AV)}
{=SUM(IF((IFERROR(YEAR(U:U),0)=2014),AV:AV))}

Both of these work and provide the answer I need, however this is a huge spread sheet and I would be more comfortable if I could put this into a sumifs statement.

=SUMIFS(AV:AV,year(U:U),2014) does not work
=SUMIFS(AV:AV,iferror(year(U:U),0),2014) also does not work

Is there something in the sumifs function's logic that is not allowing this to work or is there just some slight syntax that I'm missing here?

Any and all help is greatly appreciated.

 

[steve the fish]

You cant do that with sumif/s as you have discovered. How huge is huge? Instead of using full columns use more appropriate ranges AV1:AV10000 for example. Failing that you could use helper column with YEAR(U1) in that then use the sumif on that instead.

 

[dhsilv2]

You cant do that with sumif/s as you have discovered. How huge is huge? Instead of using full columns use more appropriate ranges AV1:AV10000 for example. Failing that you could use helper column with YEAR(U1) in that then use the sumif on that instead.

Do you know why the sumifs is failing, just for my general knowledge?

Spread sheet is about 1.8 gigs and range references lead to mistakes down the road far too often.

 

[steve the fish]

Its expecting a range for the criteria range part.

 

[Jonmo1]

You could do

=SUMIFS(AV:AV,U:U,">=1/1/2014",U:U,"<=12/31/2014")

 

[dhsilv2]

Its expecting a range for the criteria range part.

That was my first thought, but excel seems to understand it in the function arguments.

Doesn't want to let me post a picture easily, but in the range I see {1900,0,2014,2014,2014,}

 

[dhsilv2]

You could do

=SUMIFS(AV:AV,U:U,">=1/1/2014",U:U,"<=12/31/2014")

This is what I'm going to go with, but it requires me to add some hidden cells so this can be formula driven for each year. Oh well, this is why everything needs to be in SQL