sum--Frequency help

davewatson86

New Member
hi all

i have this formula: =SUM(--(FREQUENCY('1'!$B$7:$B$500,'1'!$B$7:$B$500)>0))

which gives me the total amount of invoice numbers in the desired range.

how can i tell it to only total the unique invoice numbers in the desired range that have a certain value in column L of that row i.e
column A Column F
50887011 fennell
50887011 fennell
50886969 mynett
50886314 watson
50886514 watson
50886514 watson
50886514 watson
50886514 watson
50886514 watson
50886514 watson
50886514 watson
50886514 watson
50886055 fennell
50885990 mynett
50885956 mynett

<tbody>
</tbody>

my desired result is that if: mynett is my qualifier it should be 3
watson is my qualifier it should be 1
fennell is my qualifier it should be 2

Many thanks for your help it is greatly appreciated.
regards

Dave
 

AlanY

Well-known Member
try this, Watson has 2 actually

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">50887011</td><td style=";">fennell</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">50887011</td><td style=";">fennell</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">50886969</td><td style=";">mynett</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">50886314</td><td style=";">watson</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">50886514</td><td style=";">watson</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">50886514</td><td style=";">watson</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">50886514</td><td style=";">watson</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">50886514</td><td style=";">watson</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">50886514</td><td style=";">watson</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">50886514</td><td style=";">watson</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">50886514</td><td style=";">watson</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">50886514</td><td style=";">watson</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">50886055</td><td style=";">fennell</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">50885990</td><td style=";">mynett</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">50885956</td><td style=";">mynett</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</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;background-color: #E2EFDA;;">2</td><td style=";">fennell</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">3</td><td style=";">mynett</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">2</td><td style=";">watson</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);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 width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A17</th><td style="text-align:left">{=SUM(<font color="Blue">--(<font color="Red">FREQUENCY(<font color="Green">IF(<font color="Purple">$B$1:$B$15=$B17,MATCH(<font color="Teal">$A$1:$A$15,$A$1:$A$15,0</font>)</font>),ROW(<font color="Purple">$A$1:$A$15</font>)-ROW(<font color="Purple">$A$1</font>)+1</font>)>0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

DanteAmor

Well-known Member
Just another way:


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:94.1px;" /><col style="width:86.5px;" /><col style="width:30.42px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">50887011</td><td >fennell</td><td > </td><td >fennell</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">50887011</td><td >fennell</td><td > </td><td >mynett</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">50886969</td><td >mynett</td><td > </td><td >watson</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">50886314</td><td >watson</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">50886514</td><td >watson</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">50886514</td><td >watson</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">50886514</td><td >watson</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">50886514</td><td >watson</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">50886514</td><td >watson</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">50886514</td><td >watson</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">50886514</td><td >watson</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">50886514</td><td >watson</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">50886055</td><td >fennell</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">50885990</td><td >mynett</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">50885956</td><td >mynett</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E1</td><td >{=SUM(IF($B$1:$B$15=D1,IF(MATCH($A$1:$A$15,$A$1:$A$15,0)=ROW($B$1:$B$15),1)))}</td></tr></table></td></tr></table>
 

Some videos you may like

This Week's Hot Topics

Top