How do I use a cell reference instead of a number in a countifs

[Jacksioni]

Hi,
Trying to automate a report as much as I can, and the data is all in excel. Currently drawing the number of incidents, etc from another sheet using =COUNTIFS('2025'!$AO6:$AO2000,"7")
All these are automatically turned into graphs etc for use. It all works fine when I am in the office but just want someone to be able to switch one number and it updates automatically.

Is it possible to substitute the 7 in the formula for any number put into J2, so that when J2 is changed to say 8, it the formula will then populate the incident number with week 8's total incidents.

Cheers

Jackson

 

[Eric W]

Welcome to the MrExcel forum!

Try:

=COUNTIFS('2025'!$AO6:$AO2000,J2)

for equal, or

=COUNTIFS('2025'!$AO6:$AO2000,">="&J2)

for greater than or equal.

 

[StephenCrump]

In addition to Eric's comments, be careful with "7" (text value) vs 7 (numeric value) as they are not the same.

COUNTIF() and COUNTIFS() ignore the difference, as shown below. But there are plenty of other functions where the difference matters and you won't get the results you're expecting.

	A	B
1	Text	2
2	Numeric	2
3
4	Text	Numeric
5	7	7
6	TRUE	FALSE
7	FALSE	TRUE
Sheet1

Cell Formulas
Range	Formula
B1	B1	=COUNTIF(A5:B5,7)
B2	B2	=COUNTIF(A5:B5,"7")
A6:B6	A6	=A5="7"
A7:B7	A7	=A5=7