unique count with criteria

[nsimha]

Hi,

I did a search before resorting to post this query but unfortunately the results from the search (sumproduct / frequency / countif) seemed to be confusing to me.. and therefore this post

I have sample monthly data for sales in one sheet as below..

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl133, width: 64"]Month[/TD]
[TD="class: xl134, width: 64"]Inv No.[/TD]
[/TR]
[TR]
[TD="class: xl131"]Apr-13[/TD]
[TD="class: xl132"]743[/TD]
[/TR]
[TR]
[TD="class: xl130"]Apr-13[/TD]
[TD="class: xl127"]759[/TD]
[/TR]
[TR]
[TD="class: xl130"]Apr-13[/TD]
[TD="class: xl127"]760[/TD]
[/TR]
[TR]
[TD="class: xl130"]Apr-13[/TD]
[TD="class: xl127"]760[/TD]
[/TR]
[TR]
[TD="class: xl130"]May-13[/TD]
[TD="class: xl127"]777[/TD]
[/TR]
[TR]
[TD="class: xl130"]May-13[/TD]
[TD="class: xl127"]822[/TD]
[/TR]
[TR]
[TD="class: xl130"]May-13[/TD]
[TD="class: xl127"]823[/TD]
[/TR]
[TR]
[TD="class: xl130"]May-13[/TD]
[TD="class: xl127"]823[/TD]
[/TR]
[TR]
[TD="class: xl130"]May-13[/TD]
[TD="class: xl127"]825[/TD]
[/TR]
[TR]
[TD="class: xl130"]Jun-13[/TD]
[TD="class: xl129"]1087[/TD]
[/TR]
[TR]
[TD="class: xl130"]Jun-13[/TD]
[TD="class: xl129"]1088[/TD]
[/TR]
[TR]
[TD="class: xl130"]Jun-13[/TD]
[TD="class: xl129"]1092[/TD]
[/TR]
[TR]
[TD="class: xl130"]Jun-13[/TD]
[TD="class: xl129"]1027[/TD]
[/TR]
[TR]
[TD="class: xl130"]Jun-13[/TD]
[TD="class: xl129"]1091[/TD]
[/TR]
[TR]
[TD="class: xl130"]Jun-13[/TD]
[TD="class: xl129"]1124[/TD]
[/TR]
[TR]
[TD="class: xl130"]Jun-13[/TD]
[TD="class: xl129"]1124[/TD]
[/TR]
[TR]
[TD="class: xl130"]Jun-13[/TD]
[TD="class: xl129"]1133[/TD]
[/TR]
[TR]
[TD="class: xl130"]Jun-13[/TD]
[TD="class: xl129"]1133[/TD]
[/TR]
[TR]
[TD="class: xl130"]Jun-13[/TD]
[TD="class: xl129"]1135[/TD]
[/TR]
[TR]
[TD="class: xl130"]Jul-13[/TD]
[TD="class: xl128"]1266[/TD]
[/TR]
[TR]
[TD="class: xl130"]Jul-13[/TD]
[TD="class: xl128"]1265[/TD]
[/TR]
[TR]
[TD="class: xl130"]Jul-13[/TD]
[TD="class: xl128"]1265[/TD]
[/TR]
[TR]
[TD="class: xl130"]Jul-13[/TD]
[TD="class: xl128"]1265[/TD]
[/TR]
[TR]
[TD="class: xl130"]Jul-13[/TD]
[TD="class: xl128"]1347[/TD]
[/TR]
</tbody>[/TABLE]

I have one more summary sheet which has a small table with Month in col A & Unique count in col B.. I need to arrive at the unique count of invoice nos against each month with a formula in the unique count column

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl127, width: 64"]Month
[/TD]
[TD="class: xl127, width: 64"]Unique Inv Count[/TD]
[/TR]
[TR]
[TD="class: xl128, align: right"]Apr-13[/TD]
[TD="class: xl127, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl128, align: right"]May-13[/TD]
[TD="class: xl127, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl128, align: right"]Jun-13[/TD]
[TD="class: xl127, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl128, align: right"]Jul-13[/TD]
[TD="class: xl127, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

Can somebody help?

 

[Aladin Akyurek]

Let A:B house the data, D:E the unique count processing...

E2, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($B$2:$B$25<>"",
  IF($A$2:$A$25-DAY($A$2:$A$25)+1=D2-DAY(D2)+1,
  MATCH($B$2:$B$25,$B$2:$B$25,0))),
  ROW($B$2:$B$25)-ROW($B$2)+1),1))

Also...

=SUM(IF(FREQUENCY(IF($B$2:$B$25<>"",
  IF($A$2:$A$25=D2,
  MATCH($B$2:$B$25,$B$2:$B$25,0))),
  ROW($B$2:$B$25)-ROW($B$2)+1),1))

if the dates involved are all first day dates like 1-Apr-13.

 

[nsimha]

Works perfectly. Thanks.
it will take a bit of time though for me just understand how this worked.. Thanks again

Let A:B house the data, D:E the unique count processing...

E2, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($B$2:$B$25<>"",
  IF($A$2:$A$25-DAY($A$2:$A$25)+1=D2-DAY(D2)+1,
  MATCH($B$2:$B$25,$B$2:$B$25,0))),
  ROW($B$2:$B$25)-ROW($B$2)+1),1))

Also...

=SUM(IF(FREQUENCY(IF($B$2:$B$25<>"",
  IF($A$2:$A$25=D2,
  MATCH($B$2:$B$25,$B$2:$B$25,0))),
  ROW($B$2:$B$25)-ROW($B$2)+1),1))

if the dates involved are all first day dates like 1-Apr-13.

 

[Aladin Akyurek]

Works perfectly. Thanks.

You are welcome.

it will take a bit of time though for me just understand how this worked.. Thanks again

The following link:

http://www.mrexcel.com/forum/excel-...ing-sum-if-frequency-match-2.html#post3156949

might be of help.