Ignoring autofiler hidden rows with SUMPRODUCT and SUBTOTAL

**JONNY981** · 02-18-2009, 10:52 AM

Hi, I posted a thread a while back about how to ignore hidden rows from autofilter when using formulas...

http://www.excelforum.com/excel-work...ml#post2039071

The solution worked perfectly.... But I have since had to use this on another sheet which is laid out a bit differently, I have tried everything to try and modify the formula to work on the new sheet but so far I have failed miserably!! I need to do the exact same thing on this sheet - filter the sheet by week and search the data for certain criteria ('Line' and 'Fail Reason') and sum up the total quantity, while ignoring autofilters hidden rows.

Here is an example of the sheet, along with an attempt of mine to modify the formula, which I think I have got TOTALLY wrong!

**Domenic** · 02-18-2009, 06:27 PM

Try...

B3, copied down and to Columns E, H, etc.:

=SUMPRODUCT(SUBTOTAL(3,OFFSET('Raw Data'!$D$2:$D$4000,ROW('Raw Data'!$D$2:$D$4000)-ROW('Raw Data'!$D$2),0,1)),--('Raw Data'!$D$2:$D$4000=Costs!B$1),--('Raw Data'!$E$2:$E$4000=Costs!$A3))

Hope this helps!

**JONNY981** · 02-19-2009, 04:36 AM

Thanks!! the formula works fine, but doesn't quite do what I need it to, rather than counting the instances of each fail reason I actually need it to sum up what is in the 'QTY' column for each fail reason...

For example.......

in the 'Costs' sheet cell B9 displays a result of '2' since there are two instances of 'PCB NOT POPULATED', whereas I need it do display '248', which is the sum of numbers in the 'QTY' column for Line 0 'PCB NOT POPULATED'

Sorry, I probably wasnt clear enough on that!

**Domenic** · 02-19-2009, 10:14 AM

Try the following instead...

=SUMPRODUCT(SUBTOTAL(9,OFFSET('Raw Data'!$H$2:$H$4000,ROW('Raw Data'!$H$2:$H$4000)-ROW('Raw Data'!$H$2),0,1)),--('Raw Data'!$D$2:$D$4000=Costs!B$1),--('Raw Data'!$E$2:$E$4000=Costs!$A3))

Hope this helps!