Prev: Find value in a Range of Cells and perform Conditional Formatt
Next: Switch lookup function to another sheet
From: Sam.D on 29 Jan 2010 09:21
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


From: Gary''s Student on 29 Jan 2010 10:03
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"))
--
Gary''s Student - gsnu201001


"Sam.D" wrote:

> 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
>
>
 | 
Pages: 1
Prev: Find value in a Range of Cells and perform Conditional Formatt
Next: Switch lookup function to another sheet