SubTotal Function

Question

My Table has cells having Zero values, MS Excel Subtotal Average Function (101), Count Function (102) and Count A Function (103) includes cells having Zero values while calculating average or count. Please guide how the Cells having Zero values could be excluded in Subtotal or Average Functions ?

=SUBTOTAL(101,Table5[MOM Income])

=SUBTOTAL(102,Table5[MOM Exp])

Answer 1

. . . Actually my Table has cells having Zero Values. I have inadvertently mentioned it as Blank Cells instead of Cells having Zero Values. I have edited my question so please guide me how to solve the problem.

Hi,

Please check whether the following solution is helpful:

• Formula for AVERAGE: =SUBTOTAL(109,Table5[MOM Income])/((SUBTOTAL(102,Table5[MOM Income]))-COUNTIF(Table5[MOM Income],0))

• Formula for COUNT: =SUBTOTAL(102,Table5[MOM Income])-COUNTIF(Table5[MOM Income],0)

• Formula for COUNTA: =SUBTOTAL(103,Table5[MOM Income])-COUNTIF(Table5[MOM Income],0)

Please respond if You require further assistance. I will try My best to be of help.

If I was able to help You, please mark My response as answer and helpful.

Thank You!

Answer 2

Hi,

In cell A14, enter this formula

=SUMPRODUCT((SUBTOTAL(102,OFFSET(A1,ROW(A2:A11)-ROW($A$1),)))*(A2:A11<>0)*(A2:A11))/SUMPRODUCT((SUBTOTAL(102,OFFSET(A1,ROW(A2:A11)-ROW($A$1),)))*(A2:A11<>0))

Hope this helps.

Answer 3

Hi,

Blank cells are excluded in calculations. Ensure that the cells which look like blanks are actually blank.

Answer 4

Hi,

I tried to recreate Your situation . . the formulas correctly calculated the result . . did NOT include the blank cells.

Please refer to the following screenshot.

To enable the Community to assist You, please share more information.

• Please share screenshots of Your worksheets (screenshots should include column & row headers)

and/or

• Please share sample workbook (via OneDrive, Dropbox, WeTransfer, etc.).

*please hide/delete confidential information

If I was able to help You, please mark My response as answer and helpful.

Thank You!

Answer 5

Thank you so much for your quick response and detailed reply with example.

Actually my Table has cells having Zero Values. I have inadvertently mentioned it as Blank Cells instead of Cells having Zero Values. I have edited my question so please guide me how to solve the problem.