How to get SUMPRODUCT on filtered cells

  • Thread starter Thread starter Sam.D
  • Start date Start date
S

Sam.D

I'm currently using the formula

=SUMPRODUCT((F2:I1475>=1)*(F2:I1475<=9999))

This gives me the number of the full range of cells but I need this to
update whenever I use a variety of filters.

Any help is greatly appreciated

Sam.D
 
Say we have data in A1 thru B29 like:

flia value
0 pass
2 pass
1 pass
2 pass
1 pass
2 pass
0 pass
2 pass
1 pass
2 pass
1 pass
0 pass
2 pass
0 pass
1 pass
1 fail
1 fail
0 fail
2 fail
1 fail
0 fail
1 fail
2 fail
1 fail
1 fail
1 fail
1 fail
2 fail

and are appling autofilter to column a. We want to use sumproduct to count
the "pass"es in col B when filtered:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),,1)),--(B2:B100="pass"))
 
Back
Top