sum frequency based on certain dates

Good day, I have the following data in a worksheet:

2 1 1-Apr-14
2 2 1-Apr-14
3 1 31-Mar-14
3 2 31-Mar-14
3 3 31-Mar-14
3 4 31-Mar-14
3 5 31-Mar-14
3 6 31-Mar-14
4 1 21-Dec-14
5 1 2-Feb-14
6 1 15-Jan-14
6 2 15-Jan-14
7 1 3-Apr-14
8 1 1-Mar-14
9 1 1-Jan-14

I want to count unique numbers in 1st column on a certain month of 3rd column.

Thanks in advance.
Lio.

Originally Posted by wcymiss

Assuming your data is in [A1:C15] and the certain month is 6, you could try this array formula:

Formula:

Please Login or Register  to view this content.

Enter this array formula with CTRL+SHIFT+ENTER

Good day wcymiss, thanks for you reply, i'm getting 0 as result for this array formula.

Lio.

Originally Posted by Tony Valko

Try this...

Data Range

	A	B	C	D	E	F
1	2	1	1-Apr-14		Jan	2
2	2	2	1-Apr-14		Feb	1
3	3	1	31-Mar-14		Mar	2
4	3	2	31-Mar-14		Apr	2
5	3	3	31-Mar-14		May	0
6	3	4	31-Mar-14		Jun	0
7	3	5	31-Mar-14		Jul	0
8	3	6	31-Mar-14		Aug	0
9	4	1	21-Dec-14		Sep	0
10	5	1	2-Feb-14		Oct	0
11	6	1	15-Jan-14		Nov	0
12	6	2	15-Jan-14		Dec	1
13	7	1	3-Apr-14
14	8	1	1-Mar-14
15	9	1	1-Jan-14
16	------	------	------	------	------	------

This array formula** entered in F1 and copied down:

=SUM(IF(FREQUENCY(IF(TEXT(C$1:C$15,"mmm")=E1,A$1:A$15),A$1:A$15),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thanks for your reply Tony Valko, how to do the same without the helper column? i need to do this count to fill a pre-formated dashboard data that is in other worksheet on the same workbook.

Lio.

Originally Posted by Tony Valko

What helper column? Do you mean column E with the month names? If you don't have month names how else would you do this?

Show us what results you expect.

Thanks for your reply Tony,

I tweaked your solution to make it work, here's how it looks like:

Please Login or Register  to view this content.

Thanks again for your help,
Lio