Hi guys,
at the moment I am using sumproduct in a formula like this:
SUMPRODUCT(DoD*(MONTH(DATE)=D$5)*(YEAR(DATE)=BL$5))
DoD is a name for a column in another sheet that is defined dynamically by determining the name of the sheet: ( =INDIRECT(OW!$A13&"!y4"):INDEX(INDIRECT(OW!$A13&"!y:y");MATCH(10^10;INDIRECT(OW!$A13&"!y:y"))) )
And of course D5 and BL5 make reference to a number relative to a month and a year.
So basically the formula acts as a remote subtotal, by selecting a column in another sheet, within an interval of dates, and sum up the values.
Question is, any idea how could I average these values instead of summing them up?
I know I could just add another column in the target sheet full of "1", make a sumproduct of that too, and then divide the first one by this one, but I would gladly avoid creating a new column in the target sheets
thank you in advance!
Giovanni
Bookmarks