Combining Average & Sumproduct.

  • Thread starter Thread starter Llyllyll
  • Start date Start date
L

Llyllyll

Hiya,

Please could someone help? I need to average the sum of cells where the date
is between two given dates. In the example below column N is the column I
need to sum/average and column O is the date column (formatted dd/mm/yy).

=AVERAGE(SUMPRODUCT(SUM(Main!N2:N401),Main!O2:O401>=27/3/4,Main!O2:O401<=30/
4/4,NOT(ISBLANK(Main!O2:O401))))

Unfortunately I get the #VALUE! error and don't really know where to go from
here.

Thanks
 
Although it's an array formula, I think it's a little
simpler:

=AVERAGE(IF((O2:O401>="27/3/4"*1)*
(O2:O401<="30/4/4"*1),N2:N401))

After inserting the formula into a cell, press <Enter>
while holding down the <Shift> and <Ctrl> keys.

HTH
Jason
Atlanta, GA
 
Although it's an array formula, I think it's a little
simpler:

=AVERAGE(IF((O2:O401>="27/3/4"*1)*
(O2:O401<="30/4/4"*1),N2:N401))

After inserting the formula into a cell, press <Enter>
while holding down the <Shift> and <Ctrl> keys.

HTH
Jason
Atlanta, GA
Click to expand...


Thanks, it worked a treat.
 
Back
Top