AVERAGEIF with multiple cells

[kumara_faith]

HI,

I have the following tables:

Book1
	D	E	F
5	Branch	Staff	Sales
6	Texas	Staff1	0
7	Texas	Staff2	0
8	Texas	Staff3	0
9	Texas	Staff4	0
10	Texas	Staff5	0
11	Texas	Staff6	0
12	Texas	Staff7	0
13	Texas	Staff8	0
14	Texas	Staff9	0
15	Texas	Staff10	0
16	Texas	Total	0
17	New York	Staff1	0
18	New York	Staff2	0
19	New York	Staff3	0
20	New York	Staff4	10
21	New York	Staff5	10
22	New York	Staff6	10
23	New York	Staff7	0
24	New York	Staff8	0
25	New York	Staff9	0
26	New York	Staff10	0
27	New York	Total	30
28	Dallas	Staff1	0
29	Dallas	Staff2	0
30	Dallas	Staff3	0
31	Dallas	Staff4	0
32	Dallas	Staff5	10
33	Dallas	Staff6	10
34	Dallas	Staff7	10
35	Dallas	Staff8	0
36	Dallas	Staff9	0
37	Dallas	Staff10	0
38	Dallas	Total	30
39
40	Average		20
Sheet1

Cell Formulas
Range	Formula
F16,F38,F27	F16	=SUM(F6:F15)
F40	F40	=AVERAGE(F38,F27,F16)

I am trying to summarize the average of the total sales from three locations. However, I want the average calculation to only include that location total if the total for that location is more than zero. In the above example, the correct result should be 30 which is the average for the total for Dallas and New York since Texas total is zero. I tried using AVERAGEIF but did not get the result.

Appreciate any help.

 

[Fluff]

How about

=AVERAGEIFS(F6:F38,F6:F38,">0",E6:E38,"Total")

 

[kumara_faith]

Hi Fluff,

Thank you and that worked. Appreciate it and have a great day ahead.

 

[Fluff]

You're welcome & thanks for the feedback.