VBA Excel - Summing a range of cells based on resu

Question

I am attempting create a forecasting function in excel using based on a set of preregistered historical data. I am new to VBA and I am unable to make this function.

I have three Ranges:

• Range 1 lists every day in a year. All cells are formatted in the form of "date".
• Range 2 on a different sheet named "C" also lists every day in a year. All cells are formatted in the form of "date".
• Range 3 on sheet "C" contains the dollar value actualized in the specific date of the year mentioned in Range 2.

Requirement:

The function should take 3 different variables which are the three different ranges. The Function should first "LOOKUP" the month and year of the selected cell and match it with the month of and (year -1) of the cells in Range two.

Accordingly, The cells in Range 3 on the same row in which the "LOOKUP" matches with Range 2 should sum up and then divide by the count of cells counted.

So far I have been able to create a function named MNAME.

Function MNAME(x As Variant) As String
  Dim CurrentMonth As Date
  CurrentMonth = x
  MNAME = MonthName(Month(CurrentMonth), True)
End Function

But I am failing to nest the lookups and sum up the values.

Answer 1

you dont need VBA.

Read Up on the SUMPRODUCT() function - Here is a good explanation

to summarise your problem you want to find out:

1. if the year of the cells in Range2 is the same as YEAR(reference_cell)-1

   --> IF(YEAR(reference_cell)-1=YEAR(Range2))
   

2. if the month of the cells in Range2 is the same as MONTH(reference_cell)

   --> IF(MONTH(reference_cell)=MONTH(Range2))
   

3. where 1. IS TRUE and 2. IS TRUE, sum corresponding cells in Range3

   --> =SUMPRODUCT(--(YEAR(reference_cell)-1=YEAR(Range2))*--(MONTH(reference_cell)=MONTH(Range2))*Range3)