Frequency formula

[Johnboy28]

Hi,

Is it possible to use Indirect & frequency formula together?

Need a formula to count the score totals from sheet2 using Ref "Sheet2H:H"

Thank you.

			Info from Sheet 2
Score totals		Sheet2!H:H
Score	No.				Data on sheet 2
0					Score
1	10				1
2					1
3					1
4					1
5					1
6					1
7					6
8					1
9					1
Total	10				2
					1
					1

<colgroup><col width="64" style="width: 48pt;" span="3"> <col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;"> <col width="64" style="width: 48pt;" span="3"> <tbody> </tbody>

 

[wideboydixon]

Isn't it just a COUNTIF?

Book1
	A	B
1	Score totals
2	Score totals	No.
3	0	0
4	1	10
5	2	1
6	3	0
7	4	0
8	5	0
9	6	1
10	7	0
11	8	0
12	9	0
13	Total	12
Sheet1

Cell Formulas
Range	Formula
B3	=COUNTIF(Sheet2!$H:$H,$A3)

Book1
	H
1	Score
2	1
3	1
4	1
5	1
6	1
7	1
8	6
9	1
10	1
11	2
12	1
13	1
Sheet2

WBD

 

[Johnboy28]

Isn't it just a COUNTIF?

	A	B
1	Score totals
2	Score totals	No.
3	0	0
4	1	10
5	2	1
6	3	0
7	4	0
8	5	0
9	6	1
10	7	0
11	8	0
12	9	0
13	Total	12

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B3 =COUNTIF(Sheet2!$H:$H,$A3) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

	H
1	Score
2	1
3	1
4	1
5	1
6	1
7	1
8	6
9	1
10	1
11	2
12	1
13	1

<colgroup><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

WBD

Thanks for your reply.

I wish it was that easy - I need it to reference to a single cell. The data gets refreshed and cells & columns get deleted. If I use that formula, it will come up with a error.

 

[wideboydixon]

If the cells on Sheet2 are getting moved around then try this instead:

=COUNTIF(INDEX(Sheet2!$A:$Z,,8),$A3)

WBD

 

[Johnboy28]

If the cells on Sheet2 are getting moved around then try this instead:

=COUNTIF(INDEX(Sheet2!$A:$Z,,8),$A3)

WBD

Thanks for the reply. No good - as soon as the data is refreshed and cell and columns are deleted, it comes up a error #REF !

 

[wideboydixon]

Is the entire sheet removed? With volatile data, you may need to use a volatile function:

=COUNTIF(INDIRECT("Sheet2!$H:$H"),$A3)

WBD

 

[Johnboy28]

Is the entire sheet removed? With volatile data, you may need to use a volatile function:

=COUNTIF(INDIRECT("Sheet2!$H:$H"),$A3)

WBD

Thank you - That formula works great.