Hi everyone!
So I thought I had a very easy situation on my hands, but I can't seem to figure it out. I have a large data set for customer forecasting, each part record has 6 data points.
For example, each record has:
FCST Quantity
FCST Revenue
FCST Potential
Price
FCST Market Share
What I currently have at the top of the file is SUBTOTAL(9, Ref1). Problem is that I need to drill down to the data point specifically to see the subtotal of that product type for that customer. What I would like to do is have two subtotals at the top of the sheet, one for FCST Quantity and one for FCST Potential. Of couse I thought about using SUMPRODUCT, it works but not when I add the complexity of AutoFiltering.
So I tried =SUMPRODUCT(("FCST Quantity"=M6:M1000)*(SUBTOTAL(9,R6:R1000))) and it gives me a crazy number, not correct. But it does change when I use the autofilter, which tells me I am going down the right path.
Does everyone understand the problem. I need to be able to subtotal to a specific criteria while using the autofilters....
Please help!!
Bookmarks