Forum Discussion
Need help with a multi-formula row count (Excel 365)
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.
| A | B | C | D | E | F | G | |
| 1 | Header1 | Header2 | Header3 | Header4 | Header5 | Total= | |
| 2 | 1 | 8 | |||||
| 3 | X | ||||||
| 4 | 27 | ||||||
| 5 | 55 | ||||||
| 6 | 92A | ||||||
| 7 | |||||||
| 8 | BB | ||||||
| 9 | U32 | ||||||
| 10 | H40 | 9 | C2 | ZY3 | QA33 |
5 Replies
- Hi
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
ThanksHi 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?
