Count Unique values in Column based on values in other columns

**dmschave** · 03-14-2014, 12:39 PM

Hope everyone is having a good Friday.

I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.

My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}

This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).

The results in the pink highlighted cells (Summary column D) should be:
Names starting with A - 3
All others - 2

I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.

Thanks in advance
-D

**daddylonglegs** · 03-14-2014, 08:27 PM

You should be able to combine those formulas in one like this

=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$82<$E$1,IF((Data!$A$3:$A$82=$H$1)+(Data!$A$3:$A$82=$H$1),IF(Data!$C$3:$C$82=A4,Data!$I$3:$I$82))),Data!$I$3:$I$82),1))

Note: you don't need the row functions, in fact they make the formula incorrect.

That will count the number of different dates where the other conditions are met

I'm not getting the same results as you suggest......do you want to count the date twice if it appears once for Y1 and once for Y2?

**dmschave** · 03-17-2014, 11:06 AM

Thanks for cleaning that formula up. It helps a lot to be able to work through the issue i'm having.

Yes, I want to count the number of days the data appeared in Y1 AND in Y2.

Basically I'm looking to calculate the number of days an employee worked on Y1 and Y2 so that I can divide their total results by the number of days it took to obtain those results, giving me an average for results per day.

What appears to have worked is: =SUM(IF(FREQUENCY(IF(Data!$I$3:$I$82<$E$1,IF(Data!$C$3:$C$82=A4,IF(Data!$A$3:$A$82=$I$1,Data!$I$3:$I$82))),Data!$I$3:$I$82),1))+SUM(IF(FREQUENCY(IF(Data!$I$3:$I$82<$E$1,IF(Data!$C$3:$C$82=A4,IF(Data!$A$3:$A$82=$H$1,Data!$I$3:$I$82))),Data!$I$3:$I$82),1))

Array formula of course

Basically, taking the changes you made to the original formula remove the error I was having, but still including the function twice, once for Y2 and once for Y1.

Not sure why this worked where yours didn't, but bottom line is it does ... after I get another cup or two of coffee in me I might take another look and see what is going on there.

THanks!
-D