Array w/ Sum

[Sweitz14]

=SUM((O1418:O2711=F$7)*(L1418:L2711=$B17)*$P$1418:$P$2711)

Is there a way to incorporate another argument into this
formula. I currently have to make manual changes to these
ranges for difference date ranges. ie 1418:2711 represents
the range coresponding to data for 2003. When I copy this
fomula down I have to change the range to get only 2004
data, ie o27125000.

Thanks in advance for your assistance.

 

[Frank Kabel]

Hi
try the non array function (if col. A stores your dates)
=SUMPRODUCT(($O$1:$O$10000=F$7)*($L$1:$L$10000=$B17)*(YEAR($A$1:$A$1000
0)=2004),$P$1:$P$10000)

 

[Dave R.]

Looking at this formula, it should seem fairly obvious that you can simply
add another criteria to the ones you've already got in there. You can open
up the ranges to 1 to 9999, and use that range for each argument, and add in
an argument like *(YEAR(O1:O9999)=2003) .. and of course array enter it.

 

[Sweitz14]

Yes, that was very obvious. I tried it and got a #N/A
error. Don't know why.