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.
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") |