Countif using a variable range

[itsrich]

=COUNTIF('[2022.10.14 FAB Tracking & Reporting Sheet.xlsx]Time Tracking'!$C$262:$C$701,A3)

I am attempting to make Countif change the range $C$262:$C$701 with the values in B1 & D1

The data referenced is only a sample of all the data. I am hoping I can change two cells and change the reference.

If I could write this formula as simple as possible
=COUNTIF('[2022.10.14 FAB Tracking & Reporting Sheet.xlsx]Time Tracking'!$C$B1:$C$D1,A3)

I am planning on using this in other formulas on the spreadsheet... I figure with an example, I can figure out the others.

=SUMIF('[2022.10.14 FAB Tracking & Reporting Sheet.xlsx]Time Tracking'!$C$262:$C$701,A3,'[2022.10.14 FAB Tracking & Reporting Sheet.xlsx]Time Tracking'!$S$262:$S$701)/B3

=COUNTIFS('[2022.10.14 FAB Tracking & Reporting Sheet.xlsx]Time Tracking'!$C262:$C701,A3)

TY in advance!

Book11
	A	B	C	D
1	Start Cell	262	End Cell	701
2	Model	Count
3	BATX165	6
Sheet1

Cell Formulas
Range	Formula
B3	B3	=COUNTIF('[2022.10.14 FAB Tracking & Reporting Sheet.xlsx]Time Tracking'!$C$262:$C$701,A3)

 

[JamesCanale]

I'd do it like this... I hope you can use a similar in other places on your spreadsheet.

MrExcelPlayground13.xlsx
	C	D	E
1		Look for:	Silly
2		First Row	7
3		Last Row	20
4
5	Silly		3
6	Goose
7	Silly
8	Fred
9	Harry
10	Goose
11	Fred
12	Harry
13	Silly
14	Silly
15	Goose
16	Fred
17	Goose
18	Harry
19	Goose
20	Fred
Sheet5

Cell Formulas
Range	Formula
E5	E5	=SUMPRODUCT(--(C5:C20=E1),--(ROW(C5:C20)>=E2),--(ROW(C5:C20)<=E3))

 

[Fluff]

Another way would be

=COUNTIF(INDEX('[2022.10.14 FAB Tracking & Reporting Sheet.xlsx]Time Tracking'!$C:$C,B1):INDEX('[2022.10.14 FAB Tracking & Reporting Sheet.xlsx]Time Tracking'!$C:$C,D1),A3)

 

[itsrich]

TY Fluff!

I moved the TAB to the Spreadsheet, so the formula was less confusing. Long file names are difficult to work in formulas.

Using the same INDEX formula in SUMIF of C4:C9 results in 0. C3 has the original formula.

This does not work:

=SUMIF(INDEX('Time Tracking'!$C:$C,$B$1):INDEX('Time Tracking'!$C:$C,$D$1),A5,INDEX('Time Tracking'!$C:$C,$B$1):INDEX('Time Tracking'!$C:$C,$D$1))/B5

This does work: SUMIF does not like the INDEX functions in the third argument. The 7107 in C3 is correct, the data needs to be reviewed. I am expecting double-digit numbers. Getting the formulas correct first!

=SUMIF(INDEX('Time Tracking'!$C:$C,$B$1):INDEX('Time Tracking'!$C:$C,$D$1),A4,'Time Tracking'!$S$262:$S$701)/B4

Again, ty for your knowledge ahead of time.

2022.10.14 FAB Tracking & Reporting Sheet.xlsx
	A	B	C	D
1	Start Cell	262	End Cell	701
2	Model	Count	Average Days in
3	BATX165	6	20
4	BATX175CR	12	7107
5	BATX175PRO	10	0
6	BATX195CR	1	0
7	BATX195PRO	3	0
8	BAY190CC	5	0
9	BAY210CC	13	0
Time Report

Cell Formulas
Range	Formula
B3:B9	B3	=COUNTIF(INDEX('Time Tracking'!$C:$C,$B$1):INDEX('Time Tracking'!$C:$C,$D$1),A3)
C3	C3	=SUMIF('Time Tracking'!$C$262:$C$701,A3,'Time Tracking'!$S$262:$S$701)/B3
C4	C4	=SUMIF(INDEX('Time Tracking'!$C:$C,$B$1):INDEX('Time Tracking'!$C:$C,$D$1),A4,'Time Tracking'!$S$262:$S$701)/B4
C5:C9	C5	=SUMIF(INDEX('Time Tracking'!$C:$C,$B$1):INDEX('Time Tracking'!$C:$C,$D$1),A5,INDEX('Time Tracking'!$C:$C,$B$1):INDEX('Time Tracking'!$C:$C,$D$1))/B5

 

[Fluff]

The 2nd part of that formula should be looking at col S not col C

 

[itsrich]

The 2nd part of that formula should be looking at col S not col C

GRRRR These are the type of errors that drive you up a wall, TY again Fluffy!

Again, TY for the help. This will give me the direction I need to keep me busy for a while. Now to clean up the data and turn the report into the GM.

 

[Fluff]

Glad we could help & thanks for the feedback.