1. Forum
  2. Usenet
  3. MICROSOFT.PUBLIC.EXCEL

  • matrix sum with filter

    From Claus Busch@21:1/5 to All on Wed Jun 2 10:50:37 2021
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ammammata@21:1/5 to All on Wed Jun 2 08:17:46 2021
    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 ] ...........

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ammammata@21:1/5 to All on Wed Jun 2 11:00:26 2021
    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

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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)