Array Formula to Count Unique Values

[Kiwiman]

Howzit

I would like to count the unique values in a column based on criteria in other columns. I would like to do this in an array formula or other formula as opposed to vba if possible.

In the attached file, I want to:

• Count the unique PKU values in column B
• in a 12 month period based on the value in column A of the current row
• based on the region in column C of the current row
• based on the product in column D of the current row

When I look at the data with filters applied based on my criteria for Row 2:

• Cell A2 Value = 01/02/10 therefore filter on Column A between 01/02/09 and 31/01/10
• Cell C2 Value = "Region 1" therefore filter on Column C = "Region 1"
• Cell D1 value = "Product 2" therefore filter on Column D = "Product 2"

The filters show 4 records but my desired outcome is for the formula is 3 - the 3 unique PKU values (A2, A3 and A4) in column B.

My formula in column F brings the correct filter value, but I don't know how to modify it to get the unique values.

I am using Excel 2010 in this. ANy help is very much appreciated.

 

[NBVC]

Try:

=SUM(--(FREQUENCY(IF(($A$2:$A$1674>=DATE(YEAR($A2),MONTH($A2)-11,1))*($A$2:$A$1674<=DATE(YEAR($A2),MONTH($A2)+1,0))*($C$2:$C$1674=C2)*($D$2:$D$1674=D2),IF($B$2:$B$1674<>"",MATCH($B$2:$B$1674,$B$2:$B$1674,0))),ROW($A$2:$A$1674)-ROW($A$2)+1)>0))

confirmed with CTRL+SHIFT+ENTER and copied down.

Note: These array formulas will reduce efficiency of the overall workbook....

 

[Kiwiman]

Howzit

Many thanks for your reply. That seems to have done the trick quite nicely - as far as I can understand the requirements.

The spreadsheet is already starting to buckle at the seams, so to speak, so I would imagine that they will look into an alternative solution before too long.