Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

matrix sum with filter

11 views
Skip to first unread message

Ammammata

unread,
Jun 2, 2021, 4:17:50 AM6/2/21
to
hi

I'm trying to (matrix) sum a column, where I have a filter, but it doesn't
work

=SUM(IF(E5:E10=0;0;D5:D10/E5:E10)) Ctrl-Shift-Enter is Ok, but it works on
the whole range

but

=SUBTOTAL(9;IF(E5:E10=0;0;D5:D10/E5:E10)) Ctrl-Shift-Enter fails with
#VALUE! error

Any suggestion?
I'm planning an additional column, maybe hidden, with the division result,
but I'd like to know whether there is an alternative or not.

TIA

--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

Claus Busch

unread,
Jun 2, 2021, 4:50:42 AM6/2/21
to
Hi,

Am Wed, 2 Jun 2021 08:17:46 -0000 (UTC) schrieb Ammammata:

> I'm trying to (matrix) sum a column, where I have a filter, but it doesn't
> work
>
> =SUM(IF(E5:E10=0;0;D5:D10/E5:E10)) Ctrl-Shift-Enter is Ok, but it works on
> the whole range
>
> but
>
> =SUBTOTAL(9;IF(E5:E10=0;0;D5:D10/E5:E10)) Ctrl-Shift-Enter fails with
> #VALUE! error

try:
=SUMPRODUCT(SUBTOTAL(3,INDIRECT("D"&ROW(5:10)))*(IF(E5:E10<>0,(D5:D10/E5:E10),0)))


Regards
Claus B.
--
Windows10
Microsoft 365 for business

Ammammata

unread,
Jun 2, 2021, 7:00:30 AM6/2/21
to
Il giorno Wed 02 Jun 2021 10:50:37a, *Claus Busch* ha inviato su
microsoft.public.excel il messaggio news:s97got$5dd$1...@dont-email.me.
Vediamo cosa ha scritto:

> try:
> =SUMPRODUCT(SUBTOTAL(3,INDIRECT("D"&ROW(5:10)))*(IF(E5:E10<>0,(D5:D10/E
> 5:E10),0)))
>
>

YES!
it works fine both in the small sample sheet and in big one, changing a few
references

Thank you
0 new messages