Calculating the Average of Subtotals in Excel with a formula
2015_Orders_upload.xls
So I have a list of orders which have several line items. I want to subtotal the orders so I can get an accurate count of the number of orders, then also get the average number of books per order and the average number of titles per order.
I was trying to do this with a sumif, then get an average using countif, but I can't get the sumif statement to work. I was wanting to have it add the values if their formulas contained "=subtotal" to eliminate all of the other data.
I saw another post where someone did this as a macro, but when I tried to use that, I kept getting an 'out of memory' error unless I only did like 100 rows at a time. With 15,000 rows, that seemed a bit tedious.
Any suggestions?
So I have a list of orders which have several line items. I want to subtotal the orders so I can get an accurate count of the number of orders, then also get the average number of books per order and the average number of titles per order.
I was trying to do this with a sumif, then get an average using countif, but I can't get the sumif statement to work. I was wanting to have it add the values if their formulas contained "=subtotal" to eliminate all of the other data.
I saw another post where someone did this as a macro, but when I tried to use that, I kept getting an 'out of memory' error unless I only did like 100 rows at a time. With 15,000 rows, that seemed a bit tedious.
Any suggestions?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER