Level 1

29 points

Is there a formula to count unique entries in a range of cells, and possible ignore certain predefined entries

I would like to create a formula to accomplish a task, I am not sure how to do it.

Basically, I will have cells A1-A31, with 1 corresponding with a day of the month.

Each cell A1-A31 will contain HEX data, in the form of a name (ie: John Smith) I would like another cell such as A5 to count the unique entries within the range of A1-A31.

If I can be pointed in the right direction, I can figure out for corresponding cells.

Basically it will look like this (plus other calculations, I already achieved)

DATE NAME TOTAL_CASES

1 John Smith 1

2 John Smith 1

3 Roxy Jones 2

4 Huang Nguyen 3

5 Huang Nguyen 3

6 Bradly Katz 4

7 Linda Schutlz 5

8 off 5 <--- possible addition step of ignoring "off"

9 Julie Dawgie 6

Additionally, if there is a way to count the "unique entries" in the given range, it it possible to IGNORE an item, such as the word "off" and/or "vacation?

Posted on Feb 8, 2014 7:46 PM

23 replies

SGIII

Level 8

37,580 points

Feb 11, 2014 8:33 AM in response to MikeCWest

Hi Mike,

Part of the confusion is that your sheet is evolving as you go along. That is perfectly normal. But it adds an additional challenge to suggesting ways to do what you want. I suggest you post once again your latest table, showing the column headers and letters, and how you want things to work.

MikeCWest Author

Level 1

29 points

Feb 11, 2014 12:14 PM in response to SGIII

I appreciate you patience!

This form basically counts the number of days work and days where video was obtained (creates an average DVWO/DW)

It also counts the number of cases worked, and the number of cases where video was obtained. (creates an average CVWO/CW)

It then takes those two (((DVWO/DW)+(CVWO/CW))/2). I've got those forumulas working correctly, as well as the Days Worked/Days Video Obtained cell.

Your formula to calculate the Cases Worked, works out great. The part am having an issue with is the Cases Video Obtained. I need the formula to count the number of cases that video was obtained, but only count each case once, even if video was obtained more than once on that case. It also needs to count it, even if video was not obtained on the first day.

Here is what my table looks like, with all cells showing (I cut off the blank rows on the bottom for days that have not yet occured this month.

Forumulas area filled down from row 3)

Formulas used:

Column C: IF(COUNTIFS($B$2:B3,B3,$B$2:B3,"<>" & "off")=1,1,0)

Column E: IF(D3="y",E2+1,E2+0)

Column F: IF(B3="OFF",F2+0,F2+1)

Column G: This is one one that needs work. The counts on Column G are being made manually.

Column H: H2+C3

Column I: G3÷H3

Column J: (((E3÷F3)+(G3÷H3))÷2)

Notice how video was obtained on Pamela, on the second day and not the first.

Notice how video was obtained on Vicki, but not on the first day and on a non-consectutive day.

If you also notice that on DATE column, there are two 4's. That will happen if two cases are worked in one day. It could cause a minor issue with Column F, but is not a major issue, and is easily resolved by manually enter the data if needed. (it just happens occassionally.

SGIII

Level 8

37,580 points

Feb 11, 2014 1:56 PM in response to MikeCWest

How does this work for the Cases Video Obtained column (old G new H)?

I added another hidable column, dubbed VOI ('video obtained index'), and used it to test whether that person had previously received a video.

=IF(AND(COUNTIF(D$2:D2,D3)=0,E3="Y"),MAX(H$1:H2)+1,MAX(H$1:H2))

Barry may have an easier way. But this seems to do what you want.

MikeCWest Author

Level 1

29 points

Feb 11, 2014 3:20 PM in response to SGIII

Was there a formula that was supposed to go into the new VOI column?

I entered the formula you just provided into the new "h" and the count is still not working? It still counts each name for each time video was obtained, however in your screenshot it shows correctly.

In your screen shot, it shows data in the VOI column, but mine does not show the data.

Barry

Level 9

60,035 points

Feb 11, 2014 3:41 PM in response to MikeCWest

MikeCWest wrote:

Barry,

I'm a bit more lost now.

"cases video was obtained" is supposed to be G, but you show it in H.

As far as Vicki goes, yes, the Y in row 10 should increase the count in G because it is the first time that Vicki had a Y next to her.

My row 2 which contains the 0's is for simple formulas (cell above plus 1), and in another case (cell above plus column C in current row)

Your E2 formula accomplishes the same task as my current formula in column E.

The cases wher video was obtained, still does not seem to work right. Maybe I'm missing something. I try it on several different revisions of my sheet, and each produces different results, none of which seem to be what it should.

Hi Mike,

""cases video was obtained" is supposed to be G, but you show it in H."

Sorry. Error in transferring information between your image and my table. Fixing it requires a minor change in the formula. Original version (H2 and filled down) shown, followed by revision (G2 and filled down):

Original: H2: =IF(COUNTIFS($B$2:B2,B,$D$2:D2,"Y")=1,MAX($H$1:H1)+1,MAX($H$1:H1))

Revised: G2: =IF(COUNTIFS($B$2:B2,B,$D$2:D2,"Y")=1,MAX($G$1:G1)+1,MAX($G$1:G1))

If you are keeping row 2, containing a zero in column G, use this version, starting in G3:

G3: =IF(COUNTIFS($B$3:B3,B,$D$3:D3,"Y")=1,MAX($G$2:G2)+1,MAX($G$2:G2))

I woud also suggets making row 2 a Header row

Regards,

Barry

SGIII

Level 8

37,580 points

Feb 11, 2014 3:42 PM in response to MikeCWest

Was there a formula that was supposed to go into the new VOI column?

Sorry about that. VOI is just a simple concatenation of Case Name and Was Video Obtained.

=B3&E3, filled down.

MikeCWest Author

Level 1

29 points

Feb 11, 2014 5:23 PM in response to SGIII

SG & Barry,

Thank you so much for taking time from your day(s), in order to help me.

It is finally working just great!

SGIII

Level 8

37,580 points

Feb 11, 2014 5:30 PM in response to MikeCWest

Hi Mike,

Glad it's working! Learned a lot from all participants in this thread.

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Is there a formula to count unique entries in a range of cells, and possible ignore certain predefined entries