Frequency formula

J

Johnboy28

Board Regular
Joined
Jun 22, 2013
Messages
198
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
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.


[TABLE="width: 374"]
<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>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 114, bgcolor: transparent"]Info from Sheet 2[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, width: 128, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , colspan: 2"]Score totals[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: yellow"]Sheet2!H:H[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #D9D9D9"]Score[/TD]
[TD="class: xl70, bgcolor: #D9D9D9"]No.[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]Data on sheet 2[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]0[/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: #BFBFBF"]Score[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]1[/TD]
[TD="class: xl72, bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: #DDEBF7"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]2[/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]3[/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: #DDEBF7"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]4[/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]5[/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: #DDEBF7"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]6[/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]7[/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: #DDEBF7"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]8[/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]9[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: #DDEBF7"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: #BFBFBF"]Total[/TD]
[TD="class: xl76, bgcolor: #BFBFBF"]10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: #DDEBF7"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Isn't it just a COUNTIF?


Book1
AB
1Score totals
2Score totalsNo.
300
4110
521
630
740
850
961
1070
1180
1290
13Total12
Sheet1
Cell Formulas
RangeFormula
B3=COUNTIF(Sheet2!$H:$H,$A3)



Book1
H
1Score
21
31
41
51
61
71
86
91
101
112
121
131
Sheet2


WBD
 
Upvote 0
wideboydixon said:
Isn't it just a COUNTIF?

AB
Score totals

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] "]Score totals[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] "]No.[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] "]Total[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: right"]12[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=COUNTIF(Sheet2!$H:$H,$A3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



H
1
1
1
1
1
1
6
1
1
2
1
1

<colgroup><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] "]Score[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

</tbody>
Sheet2​


WBD
Click to expand...
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.
 
Upvote 0
If the cells on Sheet2 are getting moved around then try this instead:

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

WBD
 
Upvote 0
wideboydixon said:
If the cells on Sheet2 are getting moved around then try this instead:

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

WBD
Click to expand...

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 !
 
Upvote 0
Is the entire sheet removed? With volatile data, you may need to use a volatile function:

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

WBD
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Extract the require data from Sheet1 to Sheet2 (Total Consumption For an Item for a period in Sheet1 For Several Items) in another sheet2 get in deta
2
Replies
10
Views
220
DanteAmor
DanteAmor
T
  • Question Question
Running Average of last n row if sum is between a certain value
Replies
1
Views
71
Peter_SSs
Peter_SSs
V
  • Question Question
Tracking individual codes by date and quantity
Replies
1
Views
36
Vorkosigan
V
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
570
smozgur
smozgur
markhasnomana
  • Solved
Calculate the number of survey to pass the target
Replies
8
Views
608
awoohaw
awoohaw

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

Share this page

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