Prev: rich text box in forms
Next: formula explanation
From: CousinExcel on 20 Jan 2010 09:53
Hi,

Column A | Column B | Column C
.... | 100 | $
.... | 50 | €
.... | 60 | $

=sumif(C1:C3;"$";B1:B3)
No problem until this point.

But, when I appply some filtration in column A, I need the sum to change.
When I write the formula as above, the sum does not change after filtration.

Thanks and regards,

Cousin Excel
From: Mike H on 20 Jan 2010 10:06
Hi,

Try this

=SUMPRODUCT(--(C1:C10="$"),SUBTOTAL(9,OFFSET(B1:B10,ROW(B1:B10)-MIN(ROW(B1:B10)),0,1)))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"CousinExcel" wrote:

> Hi,
>
> Column A | Column B | Column C
> ... | 100 | $
> ... | 50 | €
> ... | 60 | $
>
> =sumif(C1:C3;"$";B1:B3)
> No problem until this point.
>
> But, when I appply some filtration in column A, I need the sum to change.
> When I write the formula as above, the sum does not change after filtration.
>
> Thanks and regards,
>
> Cousin Excel
 | 
Pages: 1
Prev: rich text box in forms
Next: formula explanation