Match, lookup formula help

R

ranthrave

Board Regular
Joined
Aug 26, 2010
Messages
141
Hello sir /maam!

I have a large set of data. Kindly help me in constructing formula for absolute and relative frequency that will based only from 3 selection (area 1, area 2, and area 3)
Excel 2007<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(224, 224, 240);" width="25px"><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style="background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1</td><td style="text-align: center;">No</td><td style="text-align: center;">Location</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="text-align: center;">1</td><td style="">Branch X</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right; border-bottom: 1px solid black;">
</td><td style="text-align: right; border-bottom: 1px solid black;">
</td><td style="text-align: right; border-bottom: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">3</td><td style="text-align: center;">2</td><td style="">Branch X</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right; border-top: 1px solid black;">
</td><td style="text-align: center; border-top: 1px solid black;">frequency</td><td style="text-align: center; border-top: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">4</td><td style="text-align: center;">3</td><td style="">Branch L</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right; border-bottom: 1px solid black;">
</td><td style="text-align: center; border-bottom: 1px solid black;">absolute</td><td style="text-align: center; border-bottom: 1px solid black;">relative</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">5</td><td style="text-align: center;">4</td><td style="">Branch A</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="border-top: 1px solid black;">Area 1</td><td style="text-align: center; border-top: 1px solid black;">4</td><td style="text-align: center; border-top: 1px solid black;">26.67%</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">6</td><td style="text-align: center;">5</td><td style="">Branch L</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="">Area 2</td><td style="text-align: center;">5</td><td style="text-align: center;">33.33%</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">7</td><td style="text-align: center;">6</td><td style="">Branch A</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="border-bottom: 1px solid black;">Area 3</td><td style="text-align: center; border-bottom: 1px solid black;">6</td><td style="text-align: center; border-bottom: 1px solid black;">40.00%</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">8</td><td style="text-align: center;">7</td><td style="">Branch L</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right; border-top: 1px solid black;">
</td><td style="text-align: right; border-top: 1px solid black;">
</td><td style="text-align: right; border-top: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">9</td><td style="text-align: center;">8</td><td style="">Branch A</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">10</td><td style="text-align: center;">9</td><td style="">Branch W</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">11</td><td style="text-align: center;">10</td><td style="">Branch Y</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">12</td><td style="text-align: center;">11</td><td style="">Branch G</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">13</td><td style="text-align: center;">12</td><td style="">Branch J</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">14</td><td style="text-align: center;">13</td><td style="">Branch P</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">15</td><td style="text-align: center;">14</td><td style="">Branch Q</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">16</td><td style="text-align: center;">15</td><td style="">Branch Q</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">17</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">18</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">19</td><td style="background-color: rgb(117, 146, 60);">Area 1</td><td style="background-color: rgb(117, 146, 60);">BranchA</td><td style="text-align: right;">
</td><td style="background-color: rgb(83, 142, 213);">Area 2</td><td style="background-color: rgb(83, 142, 213);">BranchI</td><td style="text-align: right;">
</td><td style="background-color: rgb(228, 109, 10);">Area 3</td><td style="background-color: rgb(228, 109, 10);">BranchQ</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">20</td><td style="text-align: right; background-color: rgb(117, 146, 60);">
</td><td style="background-color: rgb(117, 146, 60);">BranchB</td><td style="text-align: right;">
</td><td style="text-align: right; background-color: rgb(83, 142, 213);">
</td><td style="background-color: rgb(83, 142, 213);">BranchJ</td><td style="text-align: right;">
</td><td style="text-align: right; background-color: rgb(228, 109, 10);">
</td><td style="background-color: rgb(228, 109, 10);">BranchR</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">21</td><td style="text-align: right; background-color: rgb(117, 146, 60);">
</td><td style="background-color: rgb(117, 146, 60);">BranchC</td><td style="text-align: right;">
</td><td style="text-align: right; background-color: rgb(83, 142, 213);">
</td><td style="background-color: rgb(83, 142, 213);">BranchK</td><td style="text-align: right;">
</td><td style="text-align: right; background-color: rgb(228, 109, 10);">
</td><td style="background-color: rgb(228, 109, 10);">BranchS</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">22</td><td style="text-align: right; background-color: rgb(117, 146, 60);">
</td><td style="background-color: rgb(117, 146, 60);">BranchD</td><td style="text-align: right;">
</td><td style="text-align: right; background-color: rgb(83, 142, 213);">
</td><td style="background-color: rgb(83, 142, 213);">BranchL</td><td style="text-align: right;">
</td><td style="text-align: right; background-color: rgb(228, 109, 10);">
</td><td style="background-color: rgb(228, 109, 10);">BranchT</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">23</td><td style="text-align: right; background-color: rgb(117, 146, 60);">
</td><td style="background-color: rgb(117, 146, 60);">BranchE</td><td style="text-align: right;">
</td><td style="text-align: right; background-color: rgb(83, 142, 213);">
</td><td style="background-color: rgb(83, 142, 213);">BranchM</td><td style="text-align: right;">
</td><td style="text-align: right; background-color: rgb(228, 109, 10);">
</td><td style="background-color: rgb(228, 109, 10);">BranchU</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">24</td><td style="text-align: right; background-color: rgb(117, 146, 60);">
</td><td style="background-color: rgb(117, 146, 60);">BranchF</td><td style="text-align: right;">
</td><td style="text-align: right; background-color: rgb(83, 142, 213);">
</td><td style="background-color: rgb(83, 142, 213);">BranchN</td><td style="text-align: right;">
</td><td style="text-align: right; background-color: rgb(228, 109, 10);">
</td><td style="background-color: rgb(228, 109, 10);">BranchV</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">25</td><td style="text-align: right; background-color: rgb(117, 146, 60);">
</td><td style="background-color: rgb(117, 146, 60);">BranchG</td><td style="text-align: right;">
</td><td style="text-align: right; background-color: rgb(83, 142, 213);">
</td><td style="background-color: rgb(83, 142, 213);">BranchO</td><td style="text-align: right;">
</td><td style="text-align: right; background-color: rgb(228, 109, 10);">
</td><td style="background-color: rgb(228, 109, 10);">BranchW</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">26</td><td style="text-align: right; background-color: rgb(117, 146, 60);">
</td><td style="background-color: rgb(117, 146, 60);">BranchH</td><td style="text-align: right;">
</td><td style="text-align: right; background-color: rgb(83, 142, 213);">
</td><td style="background-color: rgb(83, 142, 213);">BranchP</td><td style="text-align: right;">
</td><td style="text-align: right; background-color: rgb(228, 109, 10);">
</td><td style="background-color: rgb(228, 109, 10);">BranchX</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">27</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right; background-color: rgb(228, 109, 10);">
</td><td style="background-color: rgb(228, 109, 10);">BranchY</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">28</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right; background-color: rgb(228, 109, 10);">
</td><td style="background-color: rgb(228, 109, 10);">BranchZ</td><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">29</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td><td style="text-align: right;">
</td></tr></tbody></table>
Sheet1​


<table style="border: 2px solid black; border-collapse: collapse; padding: 0.4em; background-color: rgb(255, 255, 255);" width="85%" cellpadding="2.5px" rules="all"><tbody><tr><td style="padding: 6px;">Worksheet Formulas<table style="border: 1px solid rgb(166, 170, 182); text-align: center; background-color: rgb(255, 255, 255); border-collapse: collapse;" width="100%" cellpadding="2.5px" rules="all"><thead><tr style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);"><th width="10px">Cell</th><th style="text-align: left; padding-left: 5px;">Formula</th></tr></thead><tbody><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">I5</th><td style="text-align: left;">=4/15</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">I6</th><td style="text-align: left;">=5/15</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">I7</th><td style="text-align: left;">=6/15</td></tr></tbody></table></td></tr></tbody></table>


What could be the formula for cell H5 and I5?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Try...

H4:

=SUMPRODUCT(COUNTIF(B19:B26,SUBSTITUTE(B2:B16," ","")))

H5:

=SUMPRODUCT(COUNTIF(E19:E26,SUBSTITUTE(B2:B16," ","")))

H6:

=SUMPRODUCT(COUNTIF(H19:H28,SUBSTITUTE(B2:B16," ","")))

I4:

=H4/SUM($H$:$H$6)

Fomat I4 as Percentage and copy down.
 
Upvote 0
Aladin Akyurek said:
Try...

H4:

=SUMPRODUCT(COUNTIF(B19:B26,SUBSTITUTE(B2:B16," ","")))

H5:

=SUMPRODUCT(COUNTIF(E19:E26,SUBSTITUTE(B2:B16," ","")))

H6:

=SUMPRODUCT(COUNTIF(H19:H28,SUBSTITUTE(B2:B16," ","")))

I4:

=H4/SUM($H$:$H$6)

Fomat I4 as Percentage and copy down.
Click to expand...


Thank you very much sir :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
619
smozgur
smozgur
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
88
Dazzybeeguy
D
E
  • Question Question
VBA code clean up
Replies
9
Views
574
mumps
mumps
Dan Wilson
  • Solved
Formula help.
2
Replies
10
Views
239
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Solved
XLOOKUP with multiple criteria across multiple columns and rows
Replies
6
Views
1K
soriaul
S

Forum statistics

Threads
1,224,537
Messages
6,179,405
Members
452,911
Latest member
a_barila

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