Forum Discussion
Need help with a multi-formula row count (Excel 365)
Hi Wyn!
This is going to be needed as each row will be counted as a work order, each of the cells on the row can contain details regarding what's on the order, however not all cells on that row may be used. The end goal is that if any data was entered in any of the cells in the specified cell range on that row it will count as having been touched or updated or completed.
Ultimately I'm going to be using this formula on several sheets, however each sheet will be calculated individually. So the formula only needs to "pay attention" to the one sheet it's on.
I'd prefer it to update instantly (or after most recently updated cell is click out of), however I can make due with using a cell as a button to press to do the calculation.
For the ignoring of formulas, that would be if any of the rows being calculated have a background formula in a cell but otherwise appear blank. Essentially, if the cell on it's own (without being clicked in) appears blank to the viewer, the row counting formula will consider it blank.
See the attached file for my Power Query approach, plus I've added a more basic formula approach that will work if you can set up your sheets as Tables.
Have you used Power Query before?
No, I've not used Power Query before, but that does look the closest to what I'm going for. In the Table example it's a little off, as in that on row 7 (3rd row within the table itself) there's non-zero data in three of the cells on that row, and it calculates them individually with a result of 3 instead of 1 for that row.
I'd be interested in finding out more about Power Query, and how to include zero as valid row data to be counted (as some users may use 0 to denote they've reviewed the row but there's either nothing required to do for that row or that there's a change from a prior figure down to 0).
Can Power Query also set the refresh requirement as a clickable cell/result vs the right-click refresh?
The example sheet you created also had Excel reporting that an External Data Connection was required to be Enabled. Is this a one-time requirement to download additional functionality for Excel (such as if my Excel install was missing a macro/plug-in/etc.), or would this be required to be enabled at all times? My concern is in possible security issues arising if private data is passed outside of our internal network or that it may open a way for a possible breach into the network.
Thank you for all of your help, by the way!
The Excel table with the 3 in row 7 is in there just as a helper for the grand total in cell K3.
If you have Excel 2016 or O365 then Power Query is built in to Excel and no addin is required. If you have Excel 2013 or 2010 then you would need to download Power Query Addin onto each machine it was to run on. Everything then runs internally on that file, there is no External data connection, that message is misleading.
It can easily keep zeros by just adjusting a filter setting.
There's a refresh all button on the Data Ribbon that also works, or a macro button can also be added to trigger the refresh.