Subtotal with Averageif

[kumara_faith]

Hi,

I have the following table:

Date	Branch	Staff	Productivity Rate
Monday, 2 January, 2023	Texas	Staff1	95%
Monday, 2 January, 2023	Texas	Staff2	98%
Monday, 2 January, 2023	Texas	Staff3	0%
Monday, 2 January, 2023	Texas	Staff4	0%
Monday, 2 January, 2023	Texas	Staff5	96%
Tuesday, 3 January, 2023	Texas	Staff1	95%
Tuesday, 3 January, 2023	Texas	Staff2	0%
Tuesday, 3 January, 2023	Texas	Staff3	0%
Tuesday, 3 January, 2023	Texas	Staff4	87%
Tuesday, 3 January, 2023	Texas	Staff5	85%
Wednesday, 4 January, 2023	Texas	Staff1	0%
Wednesday, 4 January, 2023	Texas	Staff2	0%
Wednesday, 4 January, 2023	Texas	Staff3	95%
Wednesday, 4 January, 2023	Texas	Staff4	92%
Wednesday, 4 January, 2023	Texas	Staff5	93%
Subtotal	Subtotal	Subtotal	56%

I am trying to build a subtotal formula so that when we filter by dates or staff, we will be able to see the average productivity rate for the selected dates. The average productivity should however only calculate cells more than zero.

Example , for the selection below, the correct average productivity is 96% and not 56%. Is there a way to add the additional condition into this formula ? Appreciate all the help.

Date	Branch	Staff	Productivity Rate
Monday, 2 January, 2023	Texas	Staff1	95%
Monday, 2 January, 2023	Texas	Staff2	98%
Monday, 2 January, 2023	Texas	Staff3	0%
Monday, 2 January, 2023	Texas	Staff4	0%
Monday, 2 January, 2023	Texas	Staff5	96%
Subtotal	Subtotal	Subtotal	58%

 

[offthelip]

why not try something like:

=SUMIF(D2:D100,">0",D2:D23)/COUNTIF(D2:D100,">0")

 

[kumara_faith]

Hi offthelip,

Thank you for the suggestion. The reason I wanted to use subtotal is for the users to be able to filter to any dates or staff, and the productivity rate will changes accordingly by calculating the selected cells and ignoring zero. The formula suggested above will only show a static result and will not be able to change according to the selection. Appreciate the help.

 

[Fluff]

How about with a helper column

Fluff.xlsm
	A	B	C	D	E
1	Date	Branch	Staff	Productivity Rate
2	Monday, 2 January, 2023	Texas	Staff1	95%	1
3	Monday, 2 January, 2023	Texas	Staff2	98%	1
4	Monday, 2 January, 2023	Texas	Staff3	0%	1
5	Monday, 2 January, 2023	Texas	Staff4	0%	1
6	Monday, 2 January, 2023	Texas	Staff5	96%	1
7	Tuesday, 3 January, 2023	Texas	Staff1	95%	1
8	Tuesday, 3 January, 2023	Texas	Staff2	0%	1
9	Tuesday, 3 January, 2023	Texas	Staff3	0%	1
10	Tuesday, 3 January, 2023	Texas	Staff4	87%	1
11	Tuesday, 3 January, 2023	Texas	Staff5	85%	1
12	Wednesday, 4 January, 2023	Texas	Staff1	0%	1
13	Wednesday, 4 January, 2023	Texas	Staff2	0%	1
14	Wednesday, 4 January, 2023	Texas	Staff3	95%	1
15	Wednesday, 4 January, 2023	Texas	Staff4	92%	1
16	Wednesday, 4 January, 2023	Texas	Staff5	93%	1
17	Subtotal	Subtotal	Subtotal	93%
Main

Cell Formulas
Range	Formula
E2:E16	E2	=SUBTOTAL(103,A2)
D17	D17	=AVERAGEIFS(D2:D16,D2:D16,">0",E2:E16,1)

Fluff.xlsm
	A	B	C	D	E
1	Date	Branch	Staff	Productivity Rate
2	Monday, 2 January, 2023	Texas	Staff1	95%	1
3	Monday, 2 January, 2023	Texas	Staff2	98%	1
4	Monday, 2 January, 2023	Texas	Staff3	0%	1
5	Monday, 2 January, 2023	Texas	Staff4	0%	1
6	Monday, 2 January, 2023	Texas	Staff5	96%	1
17	Subtotal	Subtotal	Subtotal	96%
Main

Cell Formulas
Range	Formula
E2:E6	E2	=SUBTOTAL(103,A2)
D17	D17	=AVERAGEIFS(D2:D16,D2:D16,">0",E2:E16,1)

 

[kumara_faith]

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

 

[Fluff]

Glad we could help & thanks for the feedback.