How to calculate filtered value

[Closed]
Report
-
 Trowa -
Hello,

i have a queations regarding to excel vba

I have a excel table consist of product price and product groups (A,B,C,D) , and month

i want to know the total price depends on the group for each month
that is why 1st step that i have to do is filter the month and group. Take an exampel (group A on january) then calculate the total price of group A on January

note: the contents of excel tabel could be change anytime , so it should flexible, >>>>>>it won't work with this function"=sum(A2:A10)" because thw number of coloumn might change

is there any of you have an idea how to program it?

or maybe anyone knows how to store value that has been calculated?


thanks alot

1 reply

Hi Bebekku,

If I understand you correctly your sheet looks a bit like this:

Price	Group	Month
12	A	Jan
35	A	Feb
46	B	Ok
86	B	Jan
75	C	Feb
96	A	Feb
43	B	Jul
38	A	Jan
75	A	Feb
65	A	Okt
14	A	Nov
58	C	Dec
86	C	Jan


Now, as an example, you would like to know the total price for group A of the month Feb.
In this case the conditions is met in line 3, 7 and 10 coming to a total of 206 (35+96+75).

Use the following array formula in any cell desired outside of the array(A1:C101):
=SUM((C2:C101="Feb")*(B2:B101="A")*(A2:A101))
Confirm this formula by hitting Ctrl+Shift+Enter.

As you can see I used a larger range in the formula then actual data, so you can add more data without having to constantly change the formula. You can make the range a big as you like.

Best regards,
Trowa