Conditionally counting blank cells in a large table by referencing values in the table

[Excelerator71]

Sample Counting sheet.xlsx

(Sample sheet attached, and I have thus modified the text below to apply to this attachment.)

The concept I describe below seems like it should be possible...

So, I start with Sheet1 with a table of data in cells A2:J21. Row 1 is just headers, so my data is 10 columns * 20 rows of data.

In column E ("Owners") of that sheet, I have the names of 2 "owners" (Peter, Fred) distributed across the 20 rows, 10 rows for each owner.

I'm not allowed to modify Sheet1... so, I then go to Sheet2, which I am representing on the right side of my attached sample sheet...

In the first column of Sheet2, I start with that same list of names (Peter, Fred).

What I want in the second column of Sheet 2 is a count of all non-blank cells for all rows belonging to each person. For example, Peter has 7 blank cells distributed across his rows in Sheet 1, so I would want Peter's count to = ((10 columns * 10 Peter rows) - (7 blanks in Peter's rows)) = (100 - 7) = 93. And if Fred has 6 blanks in his 10 rows, I want his count value in Sheet 2 to = 94.

Is there one formula I can use in Sheet 2 that references their Name (sheet 2, first column) to do this kind of cell-counting from Sheet 1?

Thanks!

[tanasedn]

Hi,
It will be better if you try to attach a sample file. I'm afraid that is too much for any of us to reconstruct your worksheet.
See the yellow banner at the top of this page to see how to do this

[Excelerator71]

Thank you for the suggestion! I have modified my original post to attach a sample sheet, and I also changed all the text to apply to that sample.

[Fluff13]

How about in O3 dragged down

=ROWS(TOCOL(FILTER($A$2:$J$21,$E$2:$E$21=N3),1))

[Excelerator71]

It doesn't seem to recognize the TOCOL() portion of that formula...

[Fluff13]

Ok, you obviously don't have the new functions yet, try

=LET(f,FILTER($A$2:$J$21,$E$2:$E$21=N3),SUM(IF(f<>"",1,0)))

[Excelerator71]

It provides the correct values! Thank you!

It also seems to want to apply a weird format to the results, but I can work around that no problem.

Thanks again!

(Now to figure out how to mark this thread as resolved...)

[Fluff13]

What weird format? It just returns a number

[Excelerator71]

It makes it: Courier New, font size 9 or 10, light gray text color, left justified, and a thick black border around the cell.
The original cell was: Calibri, font size 11, regular black text, middle justified, and a thin black border around the cell.

[Fluff13]

I can only think that is something to do with copying the formula from the board. The formula itself cannot do that.

[Excelerator71]

That was exactly it. I copied the formula text to Notepad first, then copy/pasted that into the sheet, and the formatting was left alone. Thanks again - have a great day!

[Fluff13]

Glad to help & thanks for the feedback.