Sheets: COUNTUNIQUEIFS with date criteria

[jtilleyx]

I have the following formula that works:

=COUNTUNIQUEIFS(Data!F:F,Data!A:A,B1)

This references a name in B1 and reviews matches for that name in a DATA tab - column A. If it finds a match, it returns a value of how many unique departments the name appeared in - from column F in DATA tab.

I need to add one more piece to this but haven't found a way to make it work. I need the formula above to work the same, but only between specific dates. The Start Date is in A1 and the End Date is in A2 of the main tab (same tab as B1 name reference)

Any help would be greatly appreciated

 

[Jimmypop]

Hi there...

Note that this suggestion is untetsted as yet...

You can modify the formula by adding two more criteria using the COUNTIFS function to specify the date range. Here's how you can possibly modify the formula:

=COUNTUNIQUEIFS(Data!F:F,Data!A:A,B1,Data!G:G,">="&A1,Data!G:G,"<="&A2)

In this modified formula, we added two more criteria:

1. Data!G:G,">="&A1: This criteria checks if the date in column G of the DATA tab is greater than or equal to the start date in A1 of the main tab.
2. Data!G:G,"<="&A2: This criteria checks if the date in column G of the DATA tab is less than or equal to the end date in A2 of the main tab.

By using these criteria, the formula will count unique values in column F of the DATA tab that match the name in B1 AND are within the date range specified in A1 and A2 of the main tab.

I hope this helps!

 

[jtilleyx]

This works. Thank you for the help. Greatly appreciated.

 

[Jimmypop]

Hi there..

Glad we could assist and thanks for the feedback...