Question:

I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to row 1000.

What I need to be able to do is look at today's date. Determine the month and year and then look up all the values in the date column that match the month and year. I've been trying to get it to work with sumproduct but I can't wrap my head around it.

Then on a separate tab list all the unique events for that month.
So one the seperate tab it would show something like this:

May 2/2010 Bob Smith 3 Requires Attention
May 5/2010 Jim Smith 1 Out of Service

Hope you are able to help. Thanks in advance.

Question found here

Answer:

Match year and month

Array formula in A14:

Copy cell and paste it to the right to D14. Copy A14:D14 and paste it down as far as needed.

List all the unique distinct events for that month

Array formula in A23:

Copy cell and paste it to the right to D23. Copy A23:D23 and paste it down as far as needed.

How to create an array formula

1. Select cell A23
2. Copy/Paste array formula to formula bar
3. Press and hold Ctrl + Shift
4. Press Enter

Explaining array formula in cell A23

Step 1 - Find matching months and years

DATE(YEAR($C$2), MONTH($C$2), 1)=DATE(YEAR($A$5:$A$9), MONTH($A$5:$A$9), 1))

becomes

DATE(YEAR(40303), MONTH(40303), 1)=DATE(YEAR({40290;40285;40300;40303;40300}), MONTH({40290;40285;40300;40303;40300}), 1))

becomes

DATE(2010, 5, 1)=DATE({2010;2010;2010;2010;2010}, {4;4;5;5;5}), 1))

becomes

40299={40269;40269;40299;40299;40299}

and returns

{FALSE;FALSE;TRUE;TRUE;TRUE}

Step 2 - Find unique distinct records

This COUNTIFS formula avoids duplicate records. It uses absolute and relative cell references.

COUNTIFS($A$22:$A22, $A$5:$A$9, $B$22:$B22, $B$5:$B$9, $C$22:$C22, $C$5:$C$9, $D$22:$D22, $D$5:$D$9)

returns

{0;0;0;0;0}

Step 3 - Add arrays

NOT(DATE(YEAR($C$2), MONTH($C$2), 1)=DATE(YEAR($A$5:$A$9), MONTH($A$5:$A$9), 1))+COUNTIFS($A$22:$A22, $A$5:$A$9, $B$22:$B22, $B$5:$B$9, $C$22:$C22, $C$5:$C$9, $D$22:$D22, $D$5:$D$9)

becomes

NOT({FALSE;FALSE;TRUE;TRUE;TRUE})+{0;0;0;0;0}

becomes

{1;1;0;0;0}+{0;0;0;0;0}

and returns

{1;1;0;0;0}

Step 4 - Find first unique distinct row in range

MATCH(0, NOT(DATE(YEAR($C$2), MONTH($C$2), 1)=DATE(YEAR($A$5:$A$9), MONTH($A$5:$A$9), 1))+COUNTIFS($A$22:$A22, $A$5:$A$9, $B$22:$B22, $B$5:$B$9, $C$22:$C22, $C$5:$C$9, $D$22:$D22, $D$5:$D$9), 0)

becomes

MATCH(0, {1;1;0;0;0}, 0)

and returns 3.

Step 5 - Return a value of the cell at the intersection of a particular row and column

INDEX($A$5:$D$9, MATCH(0, NOT(DATE(YEAR($C$2), MONTH($C$2), 1)=DATE(YEAR($A$5:$A$9), MONTH($A$5:$A$9), 1))+COUNTIFS($A$22:$A22, $A$5:$A$9, $B$22:$B22, $B$5:$B$9, $C$22:$C22, $C$5:$C$9, $D$22:$D22, $D$5:$D$9), 0), COLUMN(A1))

becomes

INDEX($A$5:$D$9, 3, COLUMN(A1))

becomes

INDEX({40290, " Jim Smith", 1, "Out of Service"; 40285, "John Doe", 3, "Requires Attention"; 40300, " Bob Smith", 3, "Requires Attention"; 40303, " Jim Smith", 1, "Out of Service"; 40300, " Bob Smith", 3, "Requires Attention"}, 3, 1)

and returns 2-MAy-2010

IFERROR converts errors to blank cells.

Download excel *.xlsx file.

list-all-the-unique-events-for-a-specific-month.xlsx

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array,k) Returns the k-th smallest row number in this data set.

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

COLUMN(reference)
returns the column number of a reference

TEXT(value, format_text)
Converts a value to text in a specific number format

Related posts:

Excel: Calculate last date of a specific month

Extract distinct unique sorted year and month list from a date series in excel

Filter duplicates within same date, week or month in excel

Remove duplicates within same month or year in excel

Highlight duplicates on same date, week or month using conditional formatting in excel