sum--Frequency help

davewatson86

New Member
Joined
Jul 8, 2019
Messages
30
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
[TABLE="width: 157"]
<tbody>[TR]
[TD]50887011 fennell[/TD]
[/TR]
[TR]
[TD]50887011 fennell[/TD]
[/TR]
[TR]
[TD]50886969 mynett[/TD]
[/TR]
[TR]
[TD]50886314 watson[/TD]
[/TR]
[TR]
[TD]50886514 watson[/TD]
[/TR]
[TR]
[TD]50886514 watson[/TD]
[/TR]
[TR]
[TD]50886514 watson[/TD]
[/TR]
[TR]
[TD]50886514 watson[/TD]
[/TR]
[TR]
[TD]50886514 watson[/TD]
[/TR]
[TR]
[TD]50886514 watson[/TD]
[/TR]
[TR]
[TD]50886514 watson[/TD]
[/TR]
[TR]
[TD]50886514 watson[/TD]
[/TR]
[TR]
[TD]50886055 fennell[/TD]
[/TR]
[TR]
[TD]50885990 mynett[/TD]
[/TR]
[TR]
[TD]50885956 mynett[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
try this, Watson has 2 actually


Book1
AB
150887011fennell
250887011fennell
350886969mynett
450886314watson
550886514watson
650886514watson
750886514watson
850886514watson
950886514watson
1050886514watson
1150886514watson
1250886514watson
1350886055fennell
1450885990mynett
1550885956mynett
16
172fennell
183mynett
192watson
Sheet1
Cell Formulas
RangeFormula
A17{=SUM(--(FREQUENCY(IF($B$1:$B$15=$B17,MATCH($A$1:$A$15,$A$1:$A$15,0)),ROW($A$1:$A$15)-ROW($A$1)+1)>0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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