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

Month	Inv No.
Apr-13	743
Apr-13	759
Apr-13	760
Apr-13	760
May-13	777
May-13	822
May-13	823
May-13	823
May-13	825
Jun-13	1087
Jun-13	1088
Jun-13	1092
Jun-13	1027
Jun-13	1091
Jun-13	1124
Jun-13	1124
Jun-13	1133
Jun-13	1133
Jun-13	1135
Jul-13	1266
Jul-13	1265
Jul-13	1265
Jul-13	1265
Jul-13	1347

<tbody>
</tbody>

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

Month	Unique Inv Count
Apr-13	3
May-13	4
Jun-13	8
Jul-13	3

<tbody>
</tbody>

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.