Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Is there a way to sum only visible rows?

Completato
Request Demo
Correlato a SUBTOTAL
We've set up our data collection templates to include all rows/categories in a table that could have values.  However, many tables have rows that may be zero on a given quarter.  rather than breaking links to the DC templates and deleting the rows, we hide the blank rows in the document.  However, this presents the risk that in a future quarter we'll forget to unhide a row that now has a value submitted.  This would only be caught by a manual footing check on the final document.  I was hoping that i could put subtotals in the tables that sum the visible cells only (like the  =SUBTOTAL(109,...) formula in excel).  That way i could crosscheck the presented subtotal to the subtotal in the DC template and automatically catch an issue with a hidden row.  Is there a way to sum visible cells only?  

Alternatively, if there were a way to put a filter on a table to control what is visible, that would also solve our problem.  Thanks!
5

Commenti

27 commenti

Data Voti
  • Commento ufficiale
    Mike

    Great news, SUBTOTAL enthusiasts! A recent release has added some upgrades to our SUBTOTAL formula, notably that it now supports the hidden ell operators 101 thru 111. By default, this functionality is enabled though there are some caveats (listed below).

    New files or embedded tables will automatically upgrade to this formula version and any existing files (including copies) will be automatically upgraded to this formula version. Should you run into any scenarios where your formula is impacted by this change and not yet upgraded, you can reach out to your CSM or our Support team and they can assist in getting those migrated.

    Thanks much and do give us a holler if you have any questions. Happy calculating in the meantime!

     

  • Mike
    Hi Ben!

    Thanks for the question. Unfortunately, the SUBTOTAL formula is not supported Workbooks. And while it is available in Spreadsheets, the specific functions for ignoring hidden values (101-109) are also not supported as of yet. Our Product Team is looking into adding these functions and I've let them know of your interest. I'll keep you updated here with any new information going forward.

    That being said, one way I have seen our customers get around this is by including a row in the Workbook or Spreadsheet specifically for subtotals so that it will catch any hidden rows. Then you can SUMIF to catch them and either add or omit them.

    In the meantime, let me know if you have any questions on the above or need anything else. Thanks!
    0
  • Ben Andrews
    hey mike - thanks for the reply.  i'm not following how to use sumif to catch the hidden rows.  My issue right now is that the subtotal that I put in the document table includes hidden rows, so it always crosschecks against the DC template.  The only way i can think to catch a hidden row with data is via a manual (visual) footing check. 
    0
  • Mike
    Hi Ben!

    No problem. After taking a closer look at how these are set up, my suggestion of the SUMIF is only helpful if you are looking to purposefully include hidden rows by summing your subtotals. Sorry I led you astray there.

    I'm struggling to find a great way to catch these hidden values outside of checking if they foot or not. You could employ a formula that checks for that, i.e. what value is expected vs. what value is being calculated.  My apologies again for the incorrect info above. I'll keep you apprised of any updates regarding the SUBTOTAL formula in Wdesk.

    Thanks again and holler if you have questions!
    0
  • Marlon Segovia
    • Modificato

    Hello Mike,

    I imported a couple of tables in Wdesk from excel and when I create the formula to only add the visible rows in Wdesk using a filtered view it gives me an incorrect total (adds properly in excel). If I highlight the cells filtered it gives me the correct total. Checked the formatting and everything seems to be the same as excel. 

    Please advice.

    Thanks,

    Marlon

    0
  • Mike

    Hi Marlon!

    That is a yes and no. In a Workiva Spreadsheet, the function =SUBTOTAL(9,RANGE) would calculate the same as Excel, however, to clarify, that function, to my understanding, acts as a SUM that does include hidden values. The caveat here is that the function for hidden values, =SUBTOTAL(109,RANGE), is NOT supported as of yet in Workiva. Is that what you are looking for?

    Let me know if I've missed the mark there, or you have any questions for me. Thanks again and chat soon!

    0
  • Marlon Segovia

    Hi Mike thanks for your response,

    I edited my original question to be more specific after realizing it may have not been cleared but realized that after i did that, you had already responded to my original question. Please see my edited inquiry to see if it makes more sense.

     

    Thanks,

    Marlon

    0
  • Mike

    Thanks for clarifying. That helps guide to me what you are seeing.

    In Workiva, when you sum across columns or down rows and there is hidden columns/rows, it does NOT ignore them in your sum. Is this what you are looking for it to achieve? If so, this is something our Product Team is looking into enhancing, but I don't have a timeline on what that would be available.

    Hope that helps answer some of what you are seeing. Let me know if that's not the issue or you have any follow-ups for me. Thanks again and chat soon!

    0
  • Marlon Segovia

    Thanks for your help Mike, hope this feature is added soon.

    Regards,

    Marlon

    0
  • Mike

    Greetings! Finally some good news to share here. This enhancement has been added to the future roadmap. No specific timeline to share as of yet, but it's a step in the right direction. We'll continue to take in new feedback here in the meantime and will keep you apprised of progress. Let us know what questions you have in the interim. Thanks and have a super day!

    1
  • John Wilkins

    Mike,

    Great news to read there's progress! This functionality will really enhance use of shared workbooks where various users are using established filters to sort for specific data. I urge high priority within the timeline.

    Regards,

    John

    0
  • Emersius Lau

    Do add me as a requestor for this feature too. Hope to see it on the platform soon.

    0
  • Levi Gray

    Please add me as a requestor for this feature as well.

    0
  • Grant Dawson

    We would love to see this functionality in Workiva too. Hopefully it is one of those low hanging fruit items that can be implemented soon!

    0
  • Amina Sahinpasic

    Hello, can you please add me as a requestor for this feature as well? Thanks

    0
  • Mike

    You bet, Amina!

    0
  • Pedro Andrade

    Please add me as a supporter of this idea too. I've asked for the same feature 7 months ago (more details here).

    Hopefully this enhancement shall be ready soon.

    1
  • Dayna Mynarski

    Hello, can you please add me as a requestor as well? Thanks!

    0
  • Mike

    You bet, Dayna Mynarski!

    0
  • Mario Miller

    Hello, my team recently switched over to Workiva. Can you please add me as a requestor?

    Thanks!

    Mario

    0
  • Mike

    Welcome aboard, Mario! 

    0
  • Lauren Drewyer

    Hi Mike- any updates on when this is planned for?  Can you also add me as a requestor? 

    Thanks!

    Lauren

    0
  • Mike

    Hi Lauren!

    I'm happy to get you connected. We'll get that going on our end. As far as timing on when, so far I don't have a good sense yet. I don't see it likely for Q4 as of now, but its possibly it could get picked up. We'll be sure to keep you apprised either way. Thanks again and holler if you need anything!

    1
  • Ari New

    Hi-can you please also me as requestor? Thanks!

    0
  • Mike

    You bet, Ari!

    0
  • Grant Dawson

    Hi Mike. The request for this functionality goes way back and there are quite a number of requests for the "Sum visible rows only" feature (in different requests but all named or described differently). Any chance we can get this request bumped up the priority list and get an ETA?

    0
  • Mike

    Hi Grant!

    Thanks for the nudge here. Indeed, this is an oldie but a goodie. I will definitely be sure to stress the importance of this request as our product team looks at prioritizing items for the start of 2024. I'm not 100% sure on an ETA as our product folks work in quarterly schedules but I have high hopes we can get this done at some point soon. We'll be sure to let you know where this lands. Thanks again for chiming in and let me know if you have any follow-ups. Cheers!

    0

Accedi per aggiungere un commento.