Averageif with multiple cells

[kumara_faith]

Hi,

I have the following table:

Book1
	A	B	C
1	Table 1
2	Date	Branch	Revenue
3	1/1/2023	Texas	1,500
4	1/1/2023	Texas
5	1/1/2023	Texas	1,354
6	1/1/2023	Texas	1,478
7	1/1/2023	Texas
8			1,444
9
10	Table 2
11	Date	Branch	Revenue
12	1/1/2023	New York	1,500
13	1/1/2023	New York	1,500
14	1/1/2023	New York
15	1/1/2023	New York	1,478
16	1/1/2023	New York	1,500
17			1,495
18
19	Table 3
20	Date	Branch	Revenue
21	1/1/2023	Iowa
22	1/1/2023	Iowa
23	1/1/2023	Iowa
24	1/1/2023	Iowa
25	1/1/2023	Iowa
26			0
27
28	Table 4
29	1/1/2023	Summary
Sheet1

Cell Formulas
Range	Formula
C8,C26,C17	C8	=IFERROR(AVERAGEIF(C3:C7,">0",C3:C7),0)

Table 1-3 are the input tables. In Table 4,in cell C29, I am trying to calculate the average of cell C8, C17 and C26. The average should ignore the cell if the cell is blank or zero. In the above, the correct average is 1469. I tried using averageifs but I am not sure how to nest all the different cells in the formula. I appreciate any help.

 

[awoohaw]

Here is a possibility that doesnt involve redesign

Mr Excel Questions2.xlsm
	A	B	C	D
1	Table 1
2	Date	Branch	Revenue
3	1/1/2023	Texas	1500
4	1/1/2023	Texas
5	1/1/2023	Texas	1354
6	1/1/2023	Texas	1478
7	1/1/2023	Texas
8			1444
9
10	Table 2
11	Date	Branch	Revenue
12	1/1/2023	New York	1500
13	1/1/2023	New York	1500
14	1/1/2023	New York
15	1/1/2023	New York	1478
16	1/1/2023	New York	1500
17			1494.5
18
19	Table 3
20	Date	Branch	Revenue
21	1/1/2023	Iowa
22	1/1/2023	Iowa
23	1/1/2023	Iowa
24	1/1/2023	Iowa
25	1/1/2023	Iowa
26
27
28	Table 4
29	1/1/2023	Summary	1469.25
Sheet7

Cell Formulas
Range	Formula
C8,C17	C8	=SUBTOTAL(1,C3:C7)
C29	C29	=SUMPRODUCT((--(B2:B28="")),C2:C28)/ SUM((--(B2:B28="")*(--(ISNUMBER(C2:C28)))))

 

[awoohaw]

Here is a possibility that doesnt involve redesign

Mr Excel Questions2.xlsm
	A	B	C	D
1	Table 1
2	Date	Branch	Revenue
3	1/1/2023	Texas	1500
4	1/1/2023	Texas
5	1/1/2023	Texas	1354
6	1/1/2023	Texas	1478
7	1/1/2023	Texas
8			1444
9
10	Table 2
11	Date	Branch	Revenue
12	1/1/2023	New York	1500
13	1/1/2023	New York	1500
14	1/1/2023	New York
15	1/1/2023	New York	1478
16	1/1/2023	New York	1500
17			1494.5
18
19	Table 3
20	Date	Branch	Revenue
21	1/1/2023	Iowa
22	1/1/2023	Iowa
23	1/1/2023	Iowa
24	1/1/2023	Iowa
25	1/1/2023	Iowa
26
27
28	Table 4
29	1/1/2023	Summary	1469.25
Sheet7

Cell Formulas
Range	Formula
C8,C17	C8	=SUBTOTAL(1,C3:C7)
C29	C29	=SUMPRODUCT((--(B2:B28="")),C2:C28)/ SUM((--(B2:B28="")*(--(ISNUMBER(C2:C28)))))

Ignore the subtotal statements, that was something I was testing out and forgot to undo.

 

[James006]

Hi,

I am getting the result as follows:

Sorry but ....between your initial format .... and the one you have just used ... there are differences .....

 

[Alex Blakenburg]

See if you can live with this. I have hidden #DIV0! errors with conditional formatting.
BTW I doesn't seem to make sense to have a line that is an average for the section and not having a label showing it an average.

20230205 Average of Averages kumara_faith.xlsx
	A	B	C
1	Table 1
2	Date	Branch	Revenue
3	1/01/2023	Texas	1500
4	1/01/2023	Texas
5	1/01/2023	Texas	1354
6	1/01/2023	Texas	1478
7	1/01/2023	Texas
8	1/01/2023	Texas	1444
9
10	Table 2
11	Date	Branch	Revenue
12	1/01/2023	New York	1500
13	1/01/2023	New York	1500
14	1/01/2023	New York
15	1/01/2023	New York	1478
16	1/01/2023	New York	1500
17	1/01/2023	New York	1494.5
18
19	Table 3
20	Date	Branch	Revenue
21	1/01/2023	Iowa
22	1/01/2023	Iowa
23	1/01/2023	Iowa
24	1/01/2023	Iowa
25	1/01/2023	Iowa
26	1/01/2023	Iowa	#DIV/0!
27
28	Table 4
29	1/01/2023	Summary	1472.05556
Sheet2

Cell Formulas
Range	Formula
C8,C26,C17	C8	=AGGREGATE(1,0,C3:C7)
C29	C29	=AGGREGATE(1,6,C2:C26)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C8	Expression	=ISERROR($C8)	text	NO
C17	Expression	=ISERROR($C17)	text	NO
C26	Expression	=ISERROR($C26)	text	NO

 

[awoohaw]

@James006 solution worked in my worksheet. @kumara_faith , I think you just need to adjust your references andyou will be good.

 

[kumara_faith]

Hi Alex,

Thank you for the solution. You are right on the row not having any proper label. My actually layout has a staff name column and a word Total as below. My apologies for not disclosing this earlier. May I know if the aggregate formula is a heavy formula ? The reason is I have about 60,000 rows to be filled with this formula. The initial averageifs formula did not cause any lag in the spreadsheet.

Book1
	A	B	C	D
1	Table 1
2	Date	Branch	Staff Name	Revenue
3	1/1/2023	Texas	Staff1	1,500
4	1/1/2023	Texas	Staff2
5	1/1/2023	Texas	Staff3	1,354
6	1/1/2023	Texas	Staff4	1,478
7	1/1/2023	Texas	Staff5
8	1/1/2023	Texas	Total	1,444
9
10	Table 2
11	Date	Branch	Staff Name	Revenue
12	1/1/2023	New York	Staff1	1,500
13	1/1/2023	New York	Staff2	1,500
14	1/1/2023	New York	Staff3
15	1/1/2023	New York	Staff4	1,478
16	1/1/2023	New York	Staff5	1,500
17	1/1/2023	New York	Total	1,495
18
19	Table 3
20	Date	Branch	Staff Name	Revenue
21	1/1/2023	Iowa	Staff1
22	1/1/2023	Iowa	Staff2
23	1/1/2023	Iowa	Staff3
24	1/1/2023	Iowa	Staff4
25	1/1/2023	Iowa	Staff5
26	1/1/2023	Iowa	Total	#DIV/0!
27
28	Table 4
29	1/1/2023	Summary	Total	1,472
Sheet1

Cell Formulas
Range	Formula
D8,D26,D17	D8	=AGGREGATE(1,0,D3:D7)
D29	D29	=AGGREGATE(1,6,D2:D26)

 

[Alex Blakenburg]

Given that you now have additional criteria to work with you can go back to your original section formulas and use a modified version of the filter formula.

20230205 Average of Averages kumara_faith.xlsx
	A	B	C	D
1	Table 1
2	Date	Branch	Staff Name	Revenue
3	1/01/2023	Texas	Staff1	1500
4	1/01/2023	Texas	Staff2
5	1/01/2023	Texas	Staff3	1354
6	1/01/2023	Texas	Staff4	1478
7	1/01/2023	Texas	Staff5
8	1/01/2023	Texas	Total	1444
9
10	Table 2
11	Date	Branch	Staff Name	Revenue
12	1/01/2023	New York	Staff1	1500
13	1/01/2023	New York	Staff2	1500
14	1/01/2023	New York	Staff3
15	1/01/2023	New York	Staff4	1478
16	1/01/2023	New York	Staff5	1500
17	1/01/2023	New York	Total	1494.5
18
19	Table 3
20	Date	Branch	Staff Name	Revenue
21	1/01/2023	Iowa	Staff1
22	1/01/2023	Iowa	Staff2
23	1/01/2023	Iowa	Staff3
24	1/01/2023	Iowa	Staff4
25	1/01/2023	Iowa	Staff5
26	1/01/2023	Iowa	Total	0
27
28	Table 4
29	1/01/2023	Summary	Total	1472.85714
Sheet2

Cell Formulas
Range	Formula
D8,D26,D17	D8	=IFERROR(AVERAGEIF(D3:D7,">0",D3:D7),0)
D29	D29	=AVERAGE( FILTER($D$2:$D$26,($D$2:$D$26<>"") * ($D$2:$D$26<>0) * ($C$2:$C$26 <> "Total"),""))

 

[kumara_faith]

Hi Alex,

Thank you for your patience and that worked. If the users are still adamant to calculate the average of each branch average instead of the average of each branch staff, is there a way to accomplish this with the above layout?

 

[Alex Blakenburg]

Yes but I have logged out for the night, so will have to give it to you tomorrow.
If you want to try yourself try changing the <> “Total” to = “Total”

 

[Alex Blakenburg]

Yes but I have logged out for the night, so will have to give it to you tomorrow.
If you want to try yourself try changing the <> “Total” to = “Total”

I have tested this and it does what you are expecting

=AVERAGE(
       FILTER($D$2:$D$26,($D$2:$D$26<>"") * ($D$2:$D$26<>0) * ($C$2:$C$26 = "Total"),""))

At 60k records you might want to look at your overall process flow though.
It seems likely that you are inserting the section totals somehow. Nobody is going to be looking at 60k records, they are going to look at summaries. So rather than inserting section totals you are most likely better off going with @shift-del's pivot table suggestion or delve into Power Query or the Data Model and Power Pivot.