VBA calculation based on month criteria

[robert989]

Hi

I have pretty much hit a wall and hope for some help :/
My problem is quite simple, I want to calculate the average volume % for a gasmixture from a range with a month criteria so only values for the selected month is used in the calculation.

tablecalc.jpg

The Picture show three rows of values which the total volume % is calculated from and if for example one of the dates was for Another month its value should not be used in the calculation.

I havent find the right angle to solve this problem so all help is appreciated.

//Robert

[p24leclerc]

try this formula:

Please Login or Register  to view this content.

you will place in cell F1 the number of the month you want to calculate.
Please note that this formula is an array formula and that you need to end its editing by clicking CTRL+SHIFT+ENTER at the same time.
You should see those characters { and } at both ends of the formula.

[robert989]

Your formula would work in this case but I also need each true month value in the range A2:A4 to be multiplied with the corresponding B value. Thats why im looking for a VBA solution I cant figure out how to do this with excel Formulas only. Hmm

[p24leclerc]

I don't get this one. What do you mean?

[robert989]

It has to multiply each row like (B1*C1)+(B2*C2)+(B3*C3)/(B1+B2+B3) but only for the row in a specific month based on a date in the A column.
In that way I get the average for each month.

[robert989]

It has to multiply each row like (B1*C1)+(B2*C2)+(B3*C3)/(B1+B2+B3) but only for the row in a specific month based on a date in the A column.
In that way I get the average for each month.

[p24leclerc]

Isn't that exactly what my formula is doing?

The B1*C1 is calculated in cell D1. Therefore, the SUM of cells in column D that meet the month criteria are added together and
it is divided by the SUM of cells in column B that meet the same month criteria.
I don't understand what the difference is.