I would need to sum a range of cells that match a certain condition (SUMIF),
but I would need it to work as well being limited by the autofilter... anyone
to help?
I would need to sum a range of cells that match a certain condition (SUMIF),
but I would need it to work as well being limited by the autofilter... anyone
to help?
Try...
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1)),--(R
ange=Criterion),RangeToSum)
Hope this helps!
In article <[email protected]>,
Bonobo <[email protected]> wrote:
> I would need to sum a range of cells that match a certain condition (SUMIF),
> but I would need it to work as well being limited by the autofilter... anyone
> to help?
1. I am really gratefull for the help, it works!
2. I am really frustrated as I don't understand the formula...
"Domenic" wrote:
> Try...
>
> =SUMPRODUCT(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1)),--(R
> ange=Criterion),RangeToSum)
>
> Hope this helps!
>
> In article <[email protected]>,
> Bonobo <[email protected]> wrote:
>
> > I would need to sum a range of cells that match a certain condition (SUMIF),
> > but I would need it to work as well being limited by the autofilter... anyone
> > to help?
>
In article <[email protected]>,
Bonobo <[email protected]> wrote:
> 1. I am really gratefull for the help, it works!
> 2. I am really frustrated as I don't understand the formula...
Let's assume that A1:C6 contains your data, and that the data is
filtered for 'x' with the following result...
Row 1 Label1 Label3 Label2
Row 2 x red 10
Row 4 x red 12
Row 6 x blue 15
If we have the following formula...
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)),--(B
2:B6="Red"),C2:C6)
SUBTOTAL(3,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)) evaluates to:
{1;0;1;0;1}
Visible cells containing data are assigned 1 and hidden cells are
assigned 0.
--(B2:B6="Red") evaluates to:
{1;0;1;1;0}
Each cell is evaluated as TRUE or FALSE, which is then coerced by the
double negative '--' into its numerical equivalent of 1 and 0,
respectively.
C2:C6 evaluates to:
{10;16;12;18;15}
SUMPRODUCT then multiplies the evaluations...
{10;0;12;0;0}
....which it sums, and returns 22.
Hope this helps!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks