Good day,
I use the formula =COUNTIF(B4:B13;C4) in cell D4. The range B4:B13 covers 10 cells. I want to link the range to cell D1. I want to change the range based on what I type in D1. Is this possible. Thank you.
Kenny Robertson
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Change range in formula based on another cell
0
Discussion
Hello Man On Fire and welcome,
Need a bit more info to understand exactly what you want to do. Right now your formula counts how many cells in the range B4:B13 match C4. What is an example of what would be typed in D1? How do you want the range to change?
Cheers :-)
Need a bit more info to understand exactly what you want to do. Right now your formula counts how many cells in the range B4:B13 match C4. What is an example of what would be typed in D1? How do you want the range to change?
Cheers :-)
WillieD24 (rep: 687) Jun 1, '25 at 12:48 pm
@Willie - I think I know what Kenny means so have posted a simple Answer.
John_Ru (rep: 6722) Jun 1, '25 at 4:25 pm
@John,
I too suspected something similar and have posted it below.
I too suspected something similar and have posted it below.
WillieD24 (rep: 687) Jun 1, '25 at 6:29 pm
@Willie - perhaps we won't get a reply on this- I just noticed this is the second question from Man of Fire without either being Selected.
John_Ru (rep: 6722) Jun 3, '25 at 3:34 am
Thank you very much,
I have tested all three answers, and they are all working perfectly. This will save me a lot of time. Thank you all.
I have tested all three answers, and they are all working perfectly. This will save me a lot of time. Thank you all.
Man On Fire (rep: 2) Jun 3, '25 at 3:55 pm
Thanks Kenny. BTW what happened on your first question on the Forum? I couldn't see it on recent pages but you can find it easily- look at your Profile then under My Activity, click Questions and you'll have hyperlinks to all of your Questions. (If you had responsive Answers that worked, your Reputation increases if you Select the one that helped you (most).
John_Ru (rep: 6722) Jun 3, '25 at 4:18 pm
Answers
0
Selected Answer
Hi Kenny and welcome to the Forum.
You can use the worksheet function INDIRECT for this. If D1 contains valid range text e.g. B4:B13, then the range in the COUNTIF formula is replaced by the bit in bold below:
=COUNTIF(INDIRECT(D1), C4)As you alter the text in D1, the tested range will change (but you'll get a #REF error if it isn't a valid range).
REVISION #1, 02 June 2025
Given Willie has given an enhanced Answer (fixing the range if D1 is ""), here's another way to avoid the error- wrap an IFERROR function around the basic formula (changes in bold below):
= IFERROR(COUNTIF(INDIRECT(D1),C4),"Check D1")Now D4 will contain a count but state Check D1 if D1 is blank or doesn't contain a valid range (e.g. says B4-B14). You can change the error message (within the quotation marks above).
That formula is demonstrated in the attached file.
Hope this fixes things for you. If so, please remember to mark this Answer as Selected. Otherwise please check and Select Willie's Answer.
Discussion
Glad that helped, thanks for selecting my Answer, Kenny.
John_Ru (rep: 6722) Jun 3, '25 at 4:10 pm
@John
I too was beginning to think that Kenny was another poster who was looking for help, got help, but couldn't be bothered to give thanks or feedback. We've seen too many of those lately; frustrating.
Congrats on having your answer selected.
Cheers :-)
I too was beginning to think that Kenny was another poster who was looking for help, got help, but couldn't be bothered to give thanks or feedback. We've seen too many of those lately; frustrating.
Congrats on having your answer selected.
Cheers :-)
WillieD24 (rep: 687) Jun 3, '25 at 5:46 pm
@Willie - agreed and thanks
John_Ru (rep: 6722) Jun 3, '25 at 5:51 pm
0
Hi Kenny,
My answer is similar to John's but with a twist.
=IF(D1 = "", COUNTIF(B4:B13,C4), COUNTIF(INDIRECT(D1),C4))
This formula will check if D1 contains something. If not, then it will use the original COUNTIF formula. If D1 does contain something, then COUNTIF will use that range. And as John has mentioned, you will get an error if D1 is not a valid range.
If John's or my answer solves things, please mark it as Selected.
Cheers :-)