# How to exclude 0 in SUBTOTAL() function

0

I am trying to calculate the average of ACW work for each agent and I am trying the "SUBTOTAL" function but since the original data has 0, its giving error. How to make the formula omit 0

This is the sample data

Wrapup Duration

00:01:16

00:01:07

00:00:23

00:00:48

00:02:00

00:00:51

00:00:20

00:01:26

00:00:17

00:01:05

00:01:31

00:00:14

00:00:12

00:02:10

00:00:20

0

Take 3:  Here's another idea... Create a pivot table of your data.  Put AGENT in Row; WRAPUP in VALUES and WRAPUP in Report Filter ** This is where you can FILTER OUT the 0 values!  (I had some issues in getting a Total, and for some odd reason when I changed the WRAPUP in Value on Pivot table to COUNT instead of SUM, I got the individual totals.  NOTE: I also saw that there is: "E" and "E -" so when I subtotaled on A through H I didn't match the total of the total 482!

ok Take 2 ~ try entering the formula =SUMPRODUCT(SUBTOTAL(3,OFFSET(\$A\$2:\$A\$483,ROW(\$A\$2:\$A\$483)-MIN(ROW(\$A\$2:\$A\$483)),,1)),--(\$A\$2:\$A\$483="B")) in cell B490.  Remember to hit Control & enter to 'Set' the sumProduct (it will/should have the curly brackets if done correctly)

Make sure your subtotal has all the parameters in it....I copied your data, placed in Excel, created a Filtered Column for your data, then hid the BLANK cells and had similar problem with Subtotal formula.  However, if you make sure that there is a range you should get the desired results ~ Here was my formula       =SUBTOTAL(9,A2:A30).  Please give CREDIT if this has assisted you.  Thank you

### Discussion

Thanks, I tried that formula and it did work with the data I had posted but when I tried with the actual data, it again gave the result as 0. I have uploaded the actual data file for better understanding and clarity.
Sharad May 31, '17 at 12:58 am