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

[sl67]

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>

 

[lrobbo314]

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-ignoreadding; 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-ignoreadding; 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-ignoreadding; 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 xublishsource="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-ignoreadding; 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-ignoreadding; 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 xublishsource="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>

 

[Canapone]

Hi,

If a2:a17 houses real dates, a formula like

[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

 

[sl67]

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.

 

[Canapone]

Hi,

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

=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

=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.

 

[sl67]

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