Array w/ Sum

  • Thread starter Thread starter Sweitz14
  • Start date Start date
S

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 o2712:o5000.

Thanks in advance for your assistance.
 
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)
 
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.
 
Back
Top