Good afternoon,
I currently have an array formula in my workbook which basically comes down to this (which does exactly what I expect / want):
{SUM(IFERROR(((MONTH(C3:C100)=1)*(YEAR(C3:C100)=2016))*R3:R100,0))}
It calculates the sum of a range (column R) when the date (column C) contains 'January 2016'. Column C doesn't always contain a date (hence the IFERROR).
My problem is the following:
I want the value of this formula in VBA, however, I don't want to put this formula in any of my sheets. Is it possible to translate the above formula to VBA without any of my sheets containing the formula?
I succeeded with a simple for loop, but my file is a lot bigger than 97 rows, so the calculation took way too long (in comparisment, the above formula is instant when I edit it to the complete range).
Thanks in advance!
I currently have an array formula in my workbook which basically comes down to this (which does exactly what I expect / want):
{SUM(IFERROR(((MONTH(C3:C100)=1)*(YEAR(C3:C100)=2016))*R3:R100,0))}
It calculates the sum of a range (column R) when the date (column C) contains 'January 2016'. Column C doesn't always contain a date (hence the IFERROR).
My problem is the following:
I want the value of this formula in VBA, however, I don't want to put this formula in any of my sheets. Is it possible to translate the above formula to VBA without any of my sheets containing the formula?
I succeeded with a simple for loop, but my file is a lot bigger than 97 rows, so the calculation took way too long (in comparisment, the above formula is instant when I edit it to the complete range).
Thanks in advance!
Last edited:
