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.
Bookmarks