Counting non-blanks (CountA) of a matching cell's row

H

hawaean

New Member
Joined
Aug 25, 2016
Messages
36
Here's probably an easy one for you experts.

I need to count the non-blank cells in a row. The twist is I'd like the formula to also figure out which row to count from.

In my sample workbook, I have the "Overview" worksheet, which contains the rows of the table that needs counting. I have a corresponding worksheet for each name in column A of the table (Object 1, Object 2, etc...).

Looking at one of the worksheets, "Object 2", I can manually find the answer with the following:

=COUNTA(Overview!B3:H3)

Thinking about how to automate the row selection, I first turned to index/match, which can easily return the right row, but I couldn't get it to give the CountA a range.

Then, I thought maybe Offset/Address/Match would work, but excel didn't like that.

I came across a nested Indirect method that I figured could work, but the following didn't come up with the right result:

=COUNTA(INDIRECT(B14&":"&C14))

Which is too bad, because my next step would have been:

=COUNTA(INDIRECT(ADDRESS(MATCH($A$1,Overview!$A$1:$A$11,0),2,1,TRUE,"Overview")&":"&ADDRESS(MATCH($A$1,Overview!$A$1:$A$11,0),8,1,TRUE,"Overview")))

In this formula, I have the worksheet name in A1. The Indirect formulas work and return the correct start and end address, but CountA doesn't work. I'm not sure if it's a syntax issue.

I turn to you, Excel gurus! I'll try to attach my sample workbook.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
ABCDEFGH
Object 1
Object 2
Object 3
Object 4
Object 5
Object 6
Object 7
Object 8
Object 9
Object 10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2016[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]2019[/TD]
[TD="align: center"]2020[/TD]
[TD="align: center"]2021[/TD]
[TD="align: center"]2022[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]

</tbody>
Overview​




ABCD
Object 2
NOTES:

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #BFBFBF"]Count nonblanks in overview[/TD]
[TD="bgcolor: #BFBFBF, align: right"]3[/TD]
[TD="bgcolor: #BFBFBF"]<-- correct[/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #BFBFBF"]CountA using reference (not work)[/TD]
[TD="bgcolor: #BFBFBF, align: right"]1[/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #BFBFBF"]Finding the right address (row)[/TD]
[TD="bgcolor: #BFBFBF"]Overview!$B$3[/TD]
[TD="bgcolor: #BFBFBF"]Overview!$H$3[/TD]
[TD="bgcolor: #BFBFBF"]<-- correct[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #BFBFBF"]Combining formulas (not work)[/TD]
[TD="bgcolor: #BFBFBF, align: right"]1[/TD]
[TD="bgcolor: #BFBFBF, align: right"]1[/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #BFBFBF"]Reference address (not work)[/TD]
[TD="bgcolor: #BFBFBF, align: right"]1[/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]
[TD="bgcolor: #BFBFBF, align: right"][/TD]

</tbody>
Object 2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A1[/TH]
[TD="align: left"]=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=COUNTA(Overview!B3:H3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B6[/TH]
[TD="align: left"]=COUNTA(Overview!B3&":"&#REF!)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]=ADDRESS(MATCH($A$1,Overview!$A$1:$A$11,0),2,1,TRUE,"Overview")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C8[/TH]
[TD="align: left"]=ADDRESS(MATCH($A$1,Overview!$A$1:$A$11,0),8,1,TRUE,"Overview")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B9[/TH]
[TD="align: left"]=COUNTA(INDIRECT(ADDRESS(MATCH($A$1,Overview!$A$1:$A$11,0),2,1,TRUE,"Overview"))&":"&INDIRECT(ADDRESS(MATCH($A$1,Overview!$A$1:$A$11,0),8,1,TRUE,"Overview")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C9[/TH]
[TD="align: left"]=COUNTA(INDIRECT(ADDRESS(MATCH($A$1,Overview!$A$1:$A$11,0),2,1,TRUE,"Overview")&":"&ADDRESS(MATCH($A$1,Overview!$A$1:$A$11,0),8,1,TRUE,"Overview")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B10[/TH]
[TD="align: left"]=COUNTA(INDIRECT(B8&":"&C8))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Sorry if "Object 2" worksheet is confusing.

I am purely asking about getting 1 result in 1 cell. The sample sheet is just notes on my thought process, but not really what it should look like. In this case, it's just 1 cell.

Objective: Counting the number of non-blanks in a row, determined by the A1 of each worksheet.

So, for the example given, the formula should check the main table for "Object 2" and count that row.
 
Upvote 0
Perhaps post a link to the other site for reference.

Also, be aware for future that 'cross-posting' without notification to that extent is frowned upon in general.

Regards
 
Upvote 0
Thanks for the heads up, Xor LX. I am new to this. For future reference, how do I properly cross post?

XOR LX said:
Perhaps post a link to the other site for reference.

Also, be aware for future that 'cross-posting' without notification to that extent is frowned upon in general.

Regards
Click to expand...
 
Upvote 0
No worries. Just notify people that you've already posted the question on another forum, and add the link to that post.

Cheers
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Count Unique Values excluding blanks and one value
Replies
6
Views
41
Domenic
D
O
  • Solved
Dynamic (row) selection in index formula based on date selection
2
Replies
10
Views
492
DanteAmor
DanteAmor
C
  • Solved
Formula using match, indirect, and vlookup returning 0
Replies
1
Views
323
Ccruz444
C
M
  • Solved
Index and Match
Replies
3
Views
54
Peter_SSs
Peter_SSs
B
  • Question Question
Use INDIRECT combined with INDEX MATCH (multiple row criteria) to return values from specified sheet
Replies
4
Views
586
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,885
Messages
6,175,179
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top