Need help with a multi-formula row count (Excel 365)

[HarlockTaliesin]

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

[#2]

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

Thanks

[#3]

@Wyn Hopkins 

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.

[#4]

Hi @HarlockTaliesin 

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?

[#5]

@Wyn Hopkins 

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!

[#6]

Hi @HarlockTaliesin 

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.