Use a value typed into a cell as the range for a CountIF...would like to have the Cell number constant

J187

New Member
Joined
Feb 16, 2021
Messages
5
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Hi Everyone. I am using a countIF that takes its reference from cell values I typed so that I can change the portion of the table that is searched.

=COUNTIF(INDIRECT($E$5):INDIRECT($E$7),H18)

Right now, I'm entering values of "G2" and "SH12" into the cells to narrow the countIF to the range of G2:SH12. I'm looking to make this a little more efficient as the Rows will always be 2 to 12. I want to only have to type "G" and "SH" or "J" and "X" to search G2:SH12 or J2:X12. I want to include the row reference in the formula. I guess I can't do:

=COUNTIF(INDIRECT($E$5) & "2" :INDIRECT($E$7) & "1",H18)

What can I do?

Thanks!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
try
=COUNTIF(INDIRECT(E5&"2:"&E7&"12"),H18)

Book1
BCDEFGH
4
51c
618
72c
818
93
102
11
12
13
14
15
16
17
1818
19
Sheet1
Cell Formulas
RangeFormula
E10E10=COUNTIF(INDIRECT(E5&"2:"&E7&"12"),H18)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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