Sumproduct and Subtotal Question

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!!

Hi NBVC!!

It works great!!

I just don't understand how or why it works. Do you have a link to a site that will explain it?? Very interested!

Thanks again!

Matt

Hi NBVC,

I ran into a problem, my header information is on row 5 and the first data is on row 6. It seems when ever I have data in row 6, it is excluded from the SUM. Why is this??

Hello,

How can I use the SUBTOTAL on this kind of formulas?

=SUMPRODUCT((($C$12:$C$511='Data WB'!$B$1)+($C$12:$C$511='Data WB'!$C$1)+($C$12:$C$511='Data WB'!$D$1)+($C$12:$C$511='Data WB'!$E$1)+($C$12:$C$511='Data WB'!$F$1)+($C$12:$C$511='Data WB'!$G$1)+($C$12:$C$511='Data WB'!$H$1)+($C$12:$C$511='Data WB'!$I$1)+($C$12:$C$511='Data WB'!$J$1)+($C$12:$C$511='Data WB'!$K$1)+($C$12:$C$511='Data WB'!$L$1)+($C$12:$C$511='Data WB'!$M$1))*T12:T511)



Any help please ?