CODEWITHSUNDEEP
vbaexcelexcel-formula
Mario
Mario

Reputation: 177

VBA Excel - Summing a range of cells based on result in a different range of cells

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:

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.

Upvotes: 1

Views: 1440

Answers (1)

user3616725
user3616725

Reputation: 3655

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)

Upvotes: 1

Related Questions