Sum occurances of cell with a value relating to ciriteria of multiple cells with duplicate entries

sl67

New Member
Joined
Nov 23, 2011
Messages
6
Hi,

I am looking to count the numbers of clients billing within each month for each consultant.

for example; consultant C has 2 clients billing for the months of Nov-16.

Consultant B has 3 clients billing in Nov-16, and 1 on Oct-16

Any help would be appreciated.

MONTH
Consultant
Client
Sep-16
A
Name 1
Oct-16
A
Name 2
Oct-16
C
Name 3
Nov-16
C
Name 3
Nov-16
C
Name 3
Nov-16
E
Name 4
Nov-16
E
Name 4
Nov-16
B
Name 5
Nov-16
B
Name 5
Nov-16
B
Name 6
Nov-16
D
Name 7
Sep-16
B
Name 8
Oct-16
B
Name 8
Nov-16
B
Name 9
Nov-16
C
Name 10
Nov-16
C
Name 10

<tbody>
</tbody>
 
Last edited:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Just turn it into a pivot table. Put Month and Client in the Rows, and put Month in the Values as well.


<style id="Book2_16429_Styles"><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}.xl1516429 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6316429 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6416429 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:"d\\-mmm"; text-align:left; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6516429 {color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:left; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap; padding-left:9px; mso-char-indent-count:1;}--></style></head>******><!--[if !excel]>  <![endif]--><!--The following information was generated by Microsoft Excel's Publish as WebPage wizard.--><!--If the same item is republished from Excel, all information between the DIVtags will be replaced.--><!-----------------------------><!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD --><!-----------------------------><div id="Book2_16429" align=center x:publishsource="Excel"><table border=0 cellpadding=0 cellspacing=0 width=246 style='border-collapse: collapse;table-layout:fixed;width:185pt'> <col width=35 style='mso-width-source:userset;mso-width-alt:1280;width:26pt'> <col width=114 style='mso-width-source:userset;mso-width-alt:4169;width:86pt'> <col width=97 style='mso-width-source:userset;mso-width-alt:3547;width:73pt'> <tr height=20 style='height:15.0pt'> <td height=20 class=xl1516429 width=35 style='height:15.0pt;width:26pt'></td> <td class=xl6316429 width=114 style='width:86pt'>A</td> <td class=xl6316429 width=97 style='width:73pt'>B</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>3</td> <td class=xl1516429 style='font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:1.0pt solid #305496;border-right:none; border-bottom:none;border-left:none;background:#4472C4;mso-pattern:#4472C4 none'>Client Name</td> <td class=xl1516429 style='font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:1.0pt solid #305496;border-right:none; border-bottom:none;border-left:none;background:#4472C4;mso-pattern:#4472C4 none'>Month Total</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>4</td> <td class=xl6416429 style='font-size:11.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;background:#D9E1F2;mso-pattern:#D9E1F2 none'>42629</td> <td class=xl1516429 align=right style='font-size:11.0pt;color:black; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;background:#D9E1F2;mso-pattern:#D9E1F2 none'>2</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>5</td> <td class=xl6516429 style='font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri'>Name 1</td> <td class=xl1516429 align=right style='font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri'>1</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>6</td> <td class=xl6516429 style='font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri'>Name 8</td> <td class=xl1516429 align=right style='font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri'>1</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>7</td> <td class=xl6416429 style='font-size:11.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;background:#D9E1F2;mso-pattern:#D9E1F2 none'>42659</td> <td class=xl1516429 align=right style='font-size:11.0pt;color:black; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;background:#D9E1F2;mso-pattern:#D9E1F2 none'>3</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>8</td> <td class=xl6516429 style='font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri'>Name 2</td> <td class=xl1516429 align=right style='font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri'>1</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>9</td> <td class=xl6516429 style='font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri'>Name 3</td> <td class=xl1516429 align=right style='font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri'>1</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>10</td> <td class=xl6516429 style='font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri'>Name 8</td> <td class=xl1516429 align=right style='font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri'>1</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>11</td> <td class=xl6416429 style='font-size:11.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;background:#D9E1F2;mso-pattern:#D9E1F2 none'>42690</td> <td class=xl1516429 align=right style='font-size:11.0pt;color:black; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;background:#D9E1F2;mso-pattern:#D9E1F2 none'>11</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>12</td> <td class=xl6516429 style='font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri'>Name 10</td> <td class=xl1516429 align=right style='font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri'>2</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>13</td> <td class=xl6516429 style='font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri'>Name 3</td> <td class=xl1516429 align=right style='font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri'>2</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>14</td> <td class=xl6516429 style='font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri'>Name 4</td> <td class=xl1516429 align=right style='font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri'>2</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>15</td> <td class=xl6516429 style='font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri'>Name 5</td> <td class=xl1516429 align=right style='font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri'>2</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>16</td> <td class=xl6516429 style='font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri'>Name 6</td> <td class=xl1516429 align=right style='font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri'>1</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>17</td> <td class=xl6516429 style='font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri'>Name 7</td> <td class=xl1516429 align=right style='font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri'>1</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>18</td> <td class=xl6516429 style='font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri'>Name 9</td> <td class=xl1516429 align=right style='font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri'>1</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6316429 style='height:15.0pt'>19</td> <td class=xl6416429 style='font-size:11.0pt;color:black;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #305496;border-right:none; border-bottom:1.0pt solid #305496;border-left:none'>Grand Total</td> <td class=xl1516429 align=right style='font-size:11.0pt;color:black; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #305496;border-right:none; border-bottom:1.0pt solid #305496;border-left:none'>16</td> </tr> <![if supportMisalignedColumns]> <tr height=0 style='display:none'> <td width=35 style='width:26pt'></td> <td width=114 style='width:86pt'></td> <td width=97 style='width:73pt'></td> </tr> <![endif]></table></div><!-----------------------------><!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD--><!-----------------------------></body></html>
<style id="Book2_16433_Styles"><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}.xl1516433 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6316433 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}--></style></head>******><!--[if !excel]>  <![endif]--><!--The following information was generated by Microsoft Excel's Publish as WebPage wizard.--><!--If the same item is republished from Excel, all information between the DIVtags will be replaced.--><!-----------------------------><!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD --><!-----------------------------><div id="Book2_16433" align=center x:publishsource="Excel"><table border=0 cellpadding=0 cellspacing=0 width=156 style='border-collapse: collapse;table-layout:fixed;width:118pt'> <col width=78 span=2 style='mso-width-source:userset;mso-width-alt:2852; width:59pt'> <tr height=20 style='height:15.0pt'> <td colspan=2 height=20 class=xl6316433 width=156 style='height:15.0pt; width:118pt'>Formulas</td> </tr> <![if supportMisalignedColumns]> <tr height=0 style='display:none'> <td width=78 style='width:59pt'></td> <td width=78 style='width:59pt'></td> </tr> <![endif]></table></div><!-----------------------------><!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD--><!-----------------------------></body></html>
 
Last edited:
Upvote 0
Hi,

If a2:a17 houses real dates, a formula like


Code:
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=SUM(IF(FREQUENCY(IF(MONTH(A2:A17)=11,IF(B2:B17="C",MATCH(C2:C17,C2:C17,0))),MATCH(C2:C17,C2:C17,0))>0,1))[/TD]
[/TR]
</tbody>[/TABLE]


confirmed with control+shif+enter, could do the trick.



A pivot table is by far more convenient.

Regards
 
Last edited:
Upvote 0
Unfortunately pivot tables do not offer the flexibility I need.

Ive been playing round with countif for some time. I have the formula for number of clients per consultant but not slected by date.

{=SUM(IF('[book1.xlsx]sheet1'!$B$4:$B$1000=B36,1/COUNTIF('[book1.xlsx]sheet1'!$I$4:$I$1000,'[book1.xlsx]sheet1'!$I$4:$I$1000)))}

B36 is reference to cell containing a CONSULTANT reference. Column B contains CONSULTANT references, column I contains CLIENT references.

I then need to filter the result of this against the month references in column A.

Hope that makes sense.
 
Upvote 0
Hi,

I've just pasted new ranges to former formula: I cannot test now the formula (hope it works)



Code:
=SUM(IF(FREQUENCY(IF(MONTH([COLOR=#333333]'[book1.xlsx]sheet1'!$[/COLOR]A$4:$A$1000)=11,IF([COLOR=#333333]'[book1.xlsx]sheet1'!$B$4:$B$1000=B36[/COLOR],MATCH([COLOR=#333333]'[book1.xlsx]sheet1'!$I$4:$I$1000[/COLOR],[COLOR=#333333]'[book1.xlsx]sheet1'!$I$4:$I$1000[/COLOR],0))),MATCH([COLOR=#333333]'[book1.xlsx]sheet1'!$I$4:$I$1000[/COLOR],[COLOR=#333333]'[book1.xlsx]sheet1'!$I$4:$I$1000[/COLOR],0))>0,1))


or

Code:
=SUM(IF(FREQUENCY(IF([COLOR=#333333][book1.xlsx]sheet1'!$[/COLOR]A$4:$A$1000="Nov-16",IF([COLOR=#333333]'[book1.xlsx]sheet1'!$B$4:$B$1000=B36[/COLOR],MATCH([COLOR=#333333]'[book1.xlsx]sheet1'!$I$4:$I$1000[/COLOR],[COLOR=#333333]'[book1.xlsx]sheet1'!$I$4:$I$1000[/COLOR],0))),MATCH([COLOR=#333333]'[book1.xlsx]sheet1'!$I$4:$I$1000[/COLOR],[COLOR=#333333]'[book1.xlsx]sheet1'!$I$4:$I$1000[/COLOR],0))>0,1))

Array entered.


If ranges could contain empty cells, I've to add an extra control to the formula.

I hope I've not missed any brackets.
 
Last edited:
Upvote 0
Firstly many thanks for your help on this. Unfortunately though excel isn't liking the syntax and popping up the 'we found a problem with this formula' box. This is on both formulas you provided. I'm sure its something small.

Do you have any idea where im going wrong. I am following the formula almost verbatim.

Regards
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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