Dynamic COUNTA function based on user input (row #)

[ExcelKnut]

In cell C2 of sheet1 the user enters the row number of the header row for the 'new_report' tab. I need to count the number of header columns based on which row number the user enters (i.e. user enters 3 then COUNTA row 3, user enters 5 then COUNTA row 5 etc.) The formula below was my attempt at using COUNTA but it's not working--it always returns the value of 1. Any help is greatly appreciated!!!

COUNTA("new_report!"&sheet1!C2&":"&sheet1!C2)

Jeff

 

[Eric W]

Try:

=COUNTA(INDEX('New Report'!A:Z,C2,0))

 

[ExcelKnut]

WOW! Thank you Eric. That worked great.

I understand how COUNTA and INDEX work individually but I don't quite understand how they work together. If you could, please briefly explain.

 

[Eric W]

INDEX is really the key to this. We start off with the entire range 'New Report'!A:Z which has 26 columns and a million or so rows. The next 2 values of INDEX are the row and column of where to go within that range, starting at the upper left corner. So if we have INDEX('New Report'!A:Z,3,2) it would go three rows down and 2 columns over, or B3. You'll notice in the original formula I used a column reference of 0. One nice feature of INDEX is that if you use 0, it returns ALL the columns from the range.

So if C2 = 5, and our function is INDEX('New Report'!A:Z,C2,0), we go down 5 rows, take all the columns, and that resolves to 'New Report'!A5:Z5. Then once you have that, COUNTA functions just as you expect.

Glad it works for you!

 

[ExcelKnut]

Eric,

I REALLY appreciate you taking the time to explain that--very helpful!!!

Jeff

 

[jeskel]

Hi There, 1st time posting here but could really use some help.

COUNTIF(J16:J575,"on time")/COUNTA(J16:J575)*100

This formula is working but I need it to work only on visible cells. The spreadsheet is filtered and I want the averages for Late ontime and early to display and change when I change the filter.
This only stays the same when the filter is changed.

Any ideas?


- Jessy

 

[Eric W]

Hi Jessy, Welcome to the MrExcel forum!

First off, your question is substantially different from the question in this thread. For future reference, you should open a new thread when you have a new question. First, it's easier for people to find your question to answer it, and second, it keeps things neater.

But as long as I'm here, try this:

=SUMPRODUCT(--(J15:J575="on time"),SUBTOTAL(103,OFFSET(J15,ROW(J15:J575)-ROW(J15),0)))/SUBTOTAL(103,J16:J575)*100

SUBTOTAL is about the only function that's aware if a row is hidden or not, but using it can be a bit tricky (as you can see!).

Hope this helps!

 

[jeskel]

Thanks Eric, Didn't work. just get the #REF ! appreciate the help. I have now learned how to start my own thread, so hopefully will get some more help on this.

Thanks again for the reply! Have a great day.

- Jessy