Forum Discussion
Need help with a multi-formula row count (Excel 365)
Could you give a bit more background in to why you need to do this and how many different sheets this formula is going to be applied to.
Do you need the formula to instantly update?
I'm thinking Power Query could be an approach but would need to be refreshed on demand
Also, when you say "ignore formula" do you just want to ignore formula returning "" or ignore all formula
Thanks
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!
