200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
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
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
