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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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

K
  • Solved
Conditionally sum fractional cells based on column headings
Replies
5
Views
162
Cubist
Cubist
V
  • Question Question
How to Expand Components of an Assembly while preserving Dates
Replies
2
Views
76
Vog2654
V
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
228
DanteAmor
DanteAmor
I
  • Question Question
Formula to get recent payment date
Replies
4
Views
120
InvoicingNoob
I
T
  • Question Question
Running Average of last n row if sum is between a certain value
Replies
1
Views
72
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

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