07-30-2019 03:38 PM
07-30-2019 03:38 PM
I'm working on coming up with a formula for counting rows with data on a sheet, within a particular cell range (ex: A2:E100), where if there's any data (numeric, alpha, or mixed) in any of the cells (regardless of how many cells in that row have data, so long as at least one cell does) in the specified range it will count that row as "1", and deposit the answer in another cell (ex: G2).
This would also ignore blank cells and cells that contain a space or formula.
In the below example the total would be "8", as we're skipping the header row (row 1) and row 7 contains no data (or has a space or hidden formula). I'm thinking this will likely require a multiple array formula, however I'm not experienced enough with Excel at the moment to create more than somewhat basic formulas.
The best I can come up so far based on what I've already found online would start something like [ =SUMPRODUCT((A2:E10000<>"")+0) ] (without brackets), however this calculates each cell individually and doesn't ignore anything other than blank spaces. Possibly throwing in a COUNTIF function, and/or summing up all cells in range where Excel sees data and subtracting a COUNTBLANK formula.
Regardless, I'm a bit out of my depth with it, so any help would be greatly appreciated.
07-30-2019 05:12 PM
07-30-2019 05:27 PM
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.
07-30-2019 06:52 PM
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?
07-31-2019 09:28 AM
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!
07-31-2019 05:28 PM
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.