Counta using other cell reference

Question

I have these 2 formulas:

=COUNTA(F76:F79)

and

=COUNTIF(F76:F79,"Associate")+COUNTIF(F76:F79,"Regular membership")+COUNTIF(F76:F79,"Plan Sponsor Individual Membership")+COUNTIF(F76:F79,"Student")

I am using the 2 same formulas for multiple event registration lists and the range changes all the time.

I am trying to point the first value of the formula (in this case F76) to another cell content so that the F76 will comes from the content of D80. This way, I don't have to go and change the formula manually 5 times every time. Can you help?

Answer 1

Hello Michael,

Let's say that the variable is F76 and that F79 remain unchanged. If that's not the case, we can easily modify it.

The trick is to use =INDIRECT():

=COUNTA(F76:F79) then becomes =COUNTA(INDIRECT(D80&":F79")) which should automatically resolve to =COUNTA(F76:F79) if your D80 cell says F76.

Does this help?

Answer 2

Thank you, this was very helpful.

Answer 3

Hi Michel_Blanchette

I am Vijay, an Independent Advisor. I am here to work with you on this problem.

Request you to please prepare and upload a sample / dummy file sans confidential / sensitive data to Onedrive and share the link here.

HOW TO UPLOAD

1. Login to https://onedrive.live.com/ utilizing the same Login ID and Password which you have used on this forum. (or use the installed Onedrive on your computer)
2. Click Upload in the top OR drag and drop the file here.
3. After uploading, right click the file and choose share.
4. Optional but recommended - Uncheck the Allow Editing
5. Click Get a Link.
6. Copy the link and paste the link here.

Do let me know if you require any further help on this. Will be glad to help you.