Count Non-Filtered Non-Blank Rows

[thestranger66]

Hi Everyone,

I have a tricky one here that I will do my best to explain. I have multiple columns of data where the rows are entirely filled in, partially filled in, or completely blank. My original goal was to count each row that was not entirely blank.

An example like this:

iiiiA B C
1 X X X
2 iiiiX X
3
4 X

Should yield a result of 3.

I managed to adopt a function from ExcelXOR.net that helped me solve this issue which you can find here:
https://excelxor.com/2015/03/30/counting-rows-where-at-least-one-condition-is-met/

My function looks like this: {=SUM(N(MMULT(N(F41:L252<>""),TRANSPOSE(COLUMN(F41:L252)^0))>0))}, where my range of interest is F41:L252.

I need to modify the above function so that it responds to me filtering my range of interest. I have no idea how to incorporate this enhancement. I assume it will have something to do with the subtotal function, but have been unable to make any progress.

Thanks in advance to anyone who takes a look at this issue!
-Steve

 

[Marcelo Branco]

Could you provide a data sample (~5 rows) similar to your real case - 7 columns of data (columns F:L)?
How are you filtering the data (criteria)?

M.

 

[thestranger66]

In the example I provided below, I could filter any of the columns of data. If I were to filter the left-most column on "United States", I would want a result of 2. These are all the results of index-match formulas- the values are not hard coded. Please let me know if this satisfies what you were looking for.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]United Kingdom[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]5- Significant Difficulty[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]United States[/TD]
[TD]1 - No difficulty[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]United States[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]1 - No difficulty[/TD]
[TD]1 - No difficulty[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD][/TD]
[TD]5- Significant Difficulty[/TD]
[TD]1 - No difficulty[/TD]
[TD]5- Significant Difficulty[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]United States[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Let A1:C5 equal to the data area, with row 1 housing headers, required for filtering.

In D1 enter: Record evaluation

In D2 enter and copy down:

=(COUNTIFS(A2:C2,"?*")+COUNT(A2:C2)>0)+0


Now invoke:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2,ROW(D2:D5)-ROW(D2),0)),--(D2:D5=1))

 

[Marcelo Branco]

Assuming there are headers in F40:L40 and data in F41:L252 maybe this
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(F41:L251,ROW(F41:L252)-ROW(F41),0,1))>0),--(MMULT(--(G41:L252<>""),{1;1;1;1;1;1})>0))

M.

 

[thestranger66]

Hi Aladin and Marcelo,

Thank you both for your quick replies. I went with Marcelo's solution since it didn't involve a helper column. It seems to be working perfectly.

Thanks,
Steve

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

oops..there is a typo in my formula.
Correction (in blue)
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(F41:L252,ROW(F41:L252)-ROW(F41),0,1))>0),--(MMULT(--(G41:L252<>""),{1;1;1;1;1;1})>0))

M.

 

[thestranger66]

Marcelo, I appreciate you mentioning the fix posted above.

Another thing I will mention for anyone trying to use this formula in the future is that there needs to be one less "1" than the number of columns in your range. So, if you have 15 columns in your range, the bolded aspects of the formula must be modified as shown below:

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(F41:T252,ROW(F41:T252)-ROW(F41),0,1))>0),--(MMULT(--(G41:T252<>""),{1;1;1;1;1;1;1;1;1;1;1;1;1;1})>0))

Thanks.

 

[Marcelo Branco]

Another thing I will mention for anyone trying to use this formula in the future is that there needs to be one less "1" than the number of columns in your range. So, if you have 15 columns in your range, the bolded aspects of the formula must be modified as shown below:

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(F41:T252,ROW(F41:T252)-ROW(F41),0,1))>0),--(MMULT(--(G41:T252<>""),{1;1;1;1;1;1;1;1;1;1;1;1;1;1})>0))

Yes, the vertical constant array must have, exactly, as many 1s as the number of columns of the range G41:T252

M.

 

[Aladin Akyurek]

If the F range is not part of the counting...

Control+shift+enter, not just enter:

=SUM(IF(SUBTOTAL(3,OFFSET(F41,ROW(F41:F252)-ROW(F41),0,1))>0,--(MMULT(--(G41:T252<>""),TRANSPOSE(COLUMN(G41:T252)^0))>0)))

should be easier to run.