Count Unique Values if Adjacent Cell is Not Blank

1. Count Unique Values if Adjacent Cell is Not Blank

Ok, so here is an example of my worksheet.
Untitled.png

I am currently using the following formula to count the unique number of dates in column A (displayed in E5).
=SUMPRODUCT((A2:A5<>"")/COUNTIF(A2:A5,A2:A5&""))

I would like to make a change so the formula in E5 will only count days that have text (or are not blank) in column C of same row (so it's not counting days not worked yet).
I can't think of a good way to do this. Any ideas?

Thanks!

2. Re: Count Unique Values if Adjacent Cell is Not Blank

I thought I had it figured out, but it's having issues when there are duplicate dates and a different value in column C for each date.
I've attached an example worksheet if anyone can help.

Example.xlsx

3. Re: Count Unique Values if Adjacent Cell is Not Blank

Try this array formula**:

=SUM(IF(FREQUENCY(IF(C2:C10<>"",A2:A10),A2:A10),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.

4. Re: Count Unique Values if Adjacent Cell is Not Blank

Can you tell us what your expected results are?

5. Re: Count Unique Values if Adjacent Cell is Not Blank

Thanks, Tony! That seems to work.

Glenn,

The expected results for the example sheet are as follows:

If all cells in column C have values, the days worked should be 5. Each date should only be counted once, so if there are no values in C2 or C3, the result should be 4. Make sense? I was trying to use the formula in cell E6, but I couldn't figure out how to make it stop returning 0.5 for days in there twice, etc.

Thanks, all!

6. Re: Count Unique Values if Adjacent Cell is Not Blank

You're welcome. Thanks for the feedback!

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1