COUNTIFS FOR Unique Values based on Months while reading from dates

Hi all,

I have been for a long time trying to fix a tricky issue without any solution . I have applied a series of array formulas without success.

I have the column B (B2:B300000), Name: Month, Format: mmm-yy (This is the column with the Months)
I have the Column I (I2:I300000), Name: Type, Format: It includes both text and numbers. (This is the column with the types)
I have the Column T (T2:T300000), Name: Names, Format: Only characters. (This is the column with the names)
I have the Column H (H2:H300000), Name: Date happened, Format: dd/mm/yyyy. (This is the Column with the dates happened each type in Column I)

I have a reference cell: B16 --> This is the reference for the Month (its format is: mmm-yy)
I have a reference cell: A17 --> This is the reference cell for the Name (its format is: only characters)

I want to create a function (I do not know whether COUNTIFS, SUMIF, COUNT is best) where:

For the reference name and the reference month, to count the monthly unique number of types BASED ON the Dates happened (of the month in the reference cell).

For instnace, in date happened I may have lets say 33 types where some of them are duplicates. So, the formula should compute the unique values of

Here is a small example of my tricky intro:

The raw data are below:

name_column// date_happened// type_column//
NAME, 03/06/2009, LA
03/06/2009 LA
05/06/2009 LA1
11/06/2009 LA
13/06/2009 LA
13/06/2009 LA
13/06/2009 LA
16/06/2009 LA
17/06/2009 LAN
18/06/2009 LA
18/06/2009 LA
19/06/2009 LA1
20/06/2009 LAA
20/06/2009 LAT1
20/06/2009 LAA
24/06/2009 LA
24/06/2009 LAT
25/06/2009 LA
26/06/2009 LA
26/06/2009 LAA
27/06/2009 LAK
31/06/2009 LA
LAT


(the column here has only }
the unique values of types) } The unique number I want to get from the formula is 18 for the Month: Jun-09 (Ref. Cell B16)

name_column// date_happened// type_column//
NAME, 03/06/2009, LA
05/06/2009 LA1
11/06/2009 LA
13/06/2009 LA
16/06/2009 LA
17/06/2009 LAN
18/06/2009 LA
19/06/2009 LA1
20/06/2009 LAA
20/06/2009 LAT1
24/06/2009 LA
24/06/2009 LAT
25/06/2009 LA
26/06/2009 LA
26/06/2009 LAA
27/06/2009 LAK
31/06/2009 LA
31/06/2009 LAT

Any help, would be very appreciated. Thank you very much

Ηι

1)Instead of trying to explain all these, would you like to upload a sample workbook?

2) Your profile says that you use Excel 2003. So Excel 2003 with COUNTIFS...??

Hi

Sorry...but i don't understand...! Isn't number 18, the corect result in this case??

Still, i am not sure for your request....!

Maybe, something like this?

=SUMPRODUCT(((MONTH($D$2:$D$19)=MONTH(D2))*($C$2:$C$19=C2)*($E$2:$E$19=E2)))

For every date_happened, I want to get the unique number of types and in the same formula sum all these unique values for the whole month which includes all the dates_happened. I dont have reference for the type. It can be anything in my dataset. Thanks a lot

Probably is a combination of SUMPRODUCT and COUNTA but I cant make it.

I am trying to build a formula like this:

=IF(A17='RAW Data'!$T$2:$T$300000,IF('RAW Data'!$B$2:$C$300000=$B$16,(SUMPRODUCT(MONTH('RAW Data'!H2:H300000)=MONTH(B16))*COUNTA('RAW Data'!I2:I3000000))))

but I dont know how to have unique values in the part of Counta..any help?