Return a summing and averaging filtered gain or losses values.

[mmr1]

Hi,

As in a sample sheet enclosed herein the data contains three columns and headers like weeks, location and Gain or Loss, I already worked out for Sum and Average (Column C27:C30) of values gain and loss (unfiltered category)

I am looking a formula for Sum and Average (Column C32:C35) of values gain and loss (filtered category) where Sum and Average gain and loss values to be returns as per filtered headers of column A or B, for example If I apply filter to Area1 criteria of Column B then sum and Average values to be return as per filtered criteria.

Thanks for the help.

Summing and averaging filtered and unfiltered list separately for gain and losess.xlsx
	A	B	C
1
2	Weeks	Location	Gain or Loss
3	Week1	Area 1	(4,540.00)
4	Week1	Area 2	1,565.00
5	Week1	Area 3	(1,937.00)
6	Week2	Area 1	1,986.00
7	Week2	Area 2	(1,880.00)
8	Week2	Area 3	1,763.00
9	Week3	Area 1	1,808.00
10	Week3	Area 2	1,468.00
11	Week3	Area 3	(2,286.00)
12	Week4	Area 1	(2,635.00)
13	Week4	Area 2	2,451.00
14	Week4	Area 3	1,829.00
15	Week5	Area 1	(1,257.00)
16	Week5	Area 2	9,332.00
17	Week5	Area 3	11,070.00
18	Week6	Area 1	1,647.00
19	Week6	Area 2	(2,230.00)
20	Week6	Area 3	1,180.00
21	Week7	Area 1	(2,259.00)
22	Week7	Area 2	(12,036.00)
23	Week7	Area 3	6,565.00
24
25
26
27	Total Sum of Gain (Unfiltred)		42,664.00
28	Total Sum of Loss (Unfiltered)		(26,520.00)
29	Total Average of Gain (Unfiltered)		3,555.33
30	Total Average of Loss (Unfiltered)		(3,451.11)
31
32	Total Sum of Gain (filtred)
33	Total Sum of Loss (filtered)
34	Total Average of Gain (filtered)
35	Total Average of Loss (filtered)
Sheet1

Cell Formulas
Range	Formula
C27	C27	=SUMIF($C$3:$C$23,">0")
C28	C28	=SUMIF($C$4:$C$24,"<0")
C29	C29	=AVERAGEIF($C$3:$C$23,">0")
C30	C30	=AVERAGEIF($C$3:$C$23,"<0")

 

[mmr1]

Hi,

Could someone look into this query i actually seeking a formula for subtotals sum and average for positive and negative values.

Thanks,appreciate your help.

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C
1
2	Weeks	Location	Gain or Loss
3	Week1	Area 1	(4,540.00)
4	Week1	Area 2	1,565.00
5	Week1	Area 3	(1,937.00)
6	Week2	Area 1	1,986.00
7	Week2	Area 2	(1,880.00)
8	Week2	Area 3	1,763.00
9	Week3	Area 1	1,808.00
10	Week3	Area 2	1,468.00
11	Week3	Area 3	(2,286.00)
12	Week4	Area 1	(2,635.00)
13	Week4	Area 2	2,451.00
14	Week4	Area 3	1,829.00
15	Week5	Area 1	(1,257.00)
16	Week5	Area 2	9,332.00
17	Week5	Area 3	11,070.00
18	Week6	Area 1	1,647.00
19	Week6	Area 2	(2,230.00)
20	Week6	Area 3	1,180.00
21	Week7	Area 1	(2,259.00)
22	Week7	Area 2	(12,036.00)
23	Week7	Area 3	6,565.00
24
25
26
27	Total Sum of Gain (Unfiltred)		42,664.00
28	Total Sum of Loss (Unfiltered)		(31,060.00)
29	Total Average of Gain (Unfiltered)		3,555.33
30	Total Average of Loss (Unfiltered)		(3,451.11)
31
32	Total Sum of Gain (filtred)		42,664.00
33	Total Sum of Loss (filtered)		(31,060.00)
34	Total Average of Gain (filtered)		3,555.33
35	Total Average of Loss (filtered)		(3,451.11)
Clubs

Cell Formulas
Range	Formula
C27	C27	=SUMIF($C$3:$C$23,">0")
C28	C28	=SUMIF($C$3:$C$23,"<0")
C29	C29	=AVERAGEIF($C$3:$C$23,">0")
C30	C30	=AVERAGEIF($C$3:$C$23,"<0")
C32	C32	=SUMPRODUCT(SUBTOTAL(9,OFFSET($C$3,ROW($C$3:$C$23)-ROW($C$3),0,1)),--($C$3:$C$23>0))
C33	C33	=SUMPRODUCT(SUBTOTAL(9,OFFSET($C$3,ROW($C$3:$C$23)-ROW($C$3),0,1)),--($C$3:$C$23<0))
C34	C34	=AVERAGE(IF(C3:C23>0,IF(SUBTOTAL(2,OFFSET(C3,ROW(C3:C23)-ROW(C3),0)),C3:C23)))
C35	C35	=AVERAGE(IF(C3:C23<0,IF(SUBTOTAL(2,OFFSET(C3,ROW(C3:C23)-ROW(C3),0)),C3:C23)))

Weeks	Location	Gain or Loss
Week1	Area 1	(4,540.00)
Week2	Area 1	1,986.00
Week3	Area 1	1,808.00
Week4	Area 1	(2,635.00)
Week5	Area 1	(1,257.00)
Week6	Area 1	1,647.00
Week7	Area 1	(2,259.00)
Total Sum of Gain (Unfiltred)		42,664.00
Total Sum of Loss (Unfiltered)		(31,060.00)
Total Average of Gain (Unfiltered)		3,555.33
Total Average of Loss (Unfiltered)		(3,451.11)
Total Sum of Gain (filtred)		5,441.00
Total Sum of Loss (filtered)		(10,691.00)
Total Average of Gain (filtered)		1,813.67
Total Average of Loss (filtered)		(2,672.75)

 

[mmr1]

Thank you so much fluff for being supportive always and provided nice solution to the query.

 

[Fluff]

Glad to help & thanks for the feedback.