Count uniques with a condition

[dr772]

I have tried the below formula given to me yesterday...but am getting a decimal number...see data below

I want to count unique names for a certain payperiod (this data doesn't show it but there may be different payperiods in the list. G2 being a reference to a payperiod on another sheet i.e. Sheet 2 = 2/10

=SUM(IF(A2:A1000=sheet2!G2,1/(COUNTIF(e2:e1000,e2:e1000)),0))
(a) (b) (c) (d) (e)
Payperiod DATE Units ROOM NAME
2/10/07 2/8/07 sicu 502bw o'dowd
2/10/07 2/8/07 picu 307b mayorga
2/10/07 2/8/07 nscu bed 2 bowen
2/10/07 2/8/07 ctu bed16 karides
2/10/07 2/8/07 6 monti 604 hom
2/10/07 2/8/07 5 monti 513d hills
2/10/07 2/8/07 4 monti 412w golin
2/10/07 2/8/07 4 cohen 469dl ingisani
2/10/07 2/9/07 4 cohen 469dr zeitchek
2/10/07 2/9/07 4 cohen 469wl gibbard
2/10/07 2/9/07 4 cohen 469wr timpone
2/10/07 2/9/07 3 Tower 395d whelan
2/10/07 2/10/07 3 DSU 352w schwarz
2/10/07 2/10/07 3 Cohen 372w pallas
2/10/07 2/10/07 2 Cohen 268d potaphshyn
2/10/07 2/10/07 2 Cohen 268W nadim
2/10/07 2/8/07 3 Tower 393wr mayorga
2/10/07 2/8/07 SICU 502bw o'dowd

 

[xlhelp]

Hi, Diane.

If you are getting a decimal number then you are not using an array formula.

The way to do this is, type the formula and at the end press ctrl/shift/enter all together.

I get 16 as a result when I do that for 10th.

Member- AAAA Association Against Acronym Abusers

 

[dr772]

yes it worked for this controlled data but when I included another payperiod it doesn't work. I get 23.2 for 2/10/2007.
See data below same headers

2/10/07 2/8/07 sicu 502bw o'dowd
2/10/07 2/8/07 picu 307b mayorga
2/10/07 2/8/07 nscu bed 2 bowen
2/10/07 2/8/07 ctu bed16 karides
2/10/07 2/8/07 6 monti 604 hom
2/10/07 2/8/07 5 monti 513d hills
2/10/07 2/8/07 4 monti 412w golin
2/10/07 2/8/07 4 cohen 469dl ingisani
2/10/07 2/8/07 4 cohen 469dr zeitchek
2/10/07 2/8/07 4 cohen 469wl gibbard
2/10/07 2/8/07 4 cohen 469wr timpone
2/10/07 2/8/07 3 Tower 395d whelan
2/10/07 2/8/07 3 DSU 352w schwarz
2/10/07 2/8/07 3 Cohen 372w pallas
2/10/07 2/8/07 2 Cohen 268d potaphshyn
2/10/07 2/8/07 2 Cohen 268W nadim
2/10/07 2/8/07 3 Tower 393wr mayorga
2/10/07 2/8/07 SICU 502bw o'dowd
2/10/07 2/8/07 MICU 517 setton
2/10/07 2/8/07 3 Tower 393d Loskofsky
2/10/07 2/9/07 nscu bed 2 bowen
2/10/07 2/9/07 6 monti 604 hom
2/10/07 2/9/07 5 monti 513d hills
2/10/07 2/9/07 4 cohen 469dl ingrisani
2/10/07 2/9/07 4 cohen 469dr zeitchek
2/10/07 2/9/07 4 cohen 469wl gibbard
2/10/07 2/9/07 4 cohen 469wr timpone
2/10/07 2/9/07 3 DSU 352w schwarz
2/10/07 2/9/07 3 Cohen 372w pallas
2/10/07 2/9/07 2 Cohen 268d potaphshyn
2/10/07 2/9/07 3 Tower 393wr mayorga
2/10/07 2/9/07 SICU 502bw o'dowd
2/10/07 2/9/07 4 Monti 407 setton
2/10/07 2/9/07 3 Tower 393d Loskofsky
2/10/07 2/9/07 MICU 514 Singh
2/10/07 2/9/07 2 DSU 246 Romanoff
2/10/07 2/10/07 6 Monti 604 hom
2/10/07 2/10/07 4 Cohen 469dl ingrisani
2/10/07 2/10/07 4 Cohen 469dr zeitchek
2/10/07 2/10/07 4 Cohen 469wl gibbard
2/10/07 2/10/07 4 cohen 469wr timpone
2/10/07 2/10/07 3 DSU 352w schwarz
2/10/07 2/10/07 3 Cohen 372w pallas
2/10/07 2/10/07 2 Cohen 268D potapshyn
2/10/07 2/10/07 4 Monti 417 o'dowd
2/10/07 2/10/07 4 Monti 407 setton
2/10/07 2/10/07 MICU 524bd Singh
2/10/07 2/10/07 2 DSU 246 Romanoff
2/10/07 2/10/07 MICU 524 Semenella
2/10/07 2/10/07 4 DSU 451W Glicken
2/10/07 2/10/07 7 Tower 712D Lee
2/24/07 2/11/07 6 Monti 604 hom
2/24/07 2/11/07 4 Cohen 469dl ingrisani
2/24/07 2/11/07 4 Cohen 469dr zeitchek
2/24/07 2/11/07 4 Cohen 469wl gibbard
2/24/07 2/11/07 4 cohen 469wr timpone
2/24/07 2/11/07 3 DSU 344d schwarz
2/24/07 2/11/07 3 Cohen 372w pallas

 

[xlhelp]

Sorry, I have been unable to find a reason for the decimal values. If I find something, I will post.

Member- AAAA Association Against Acronym Abusers

 

[Lilliabeth]

I did not find an answer to the question, but found it interesting that the countif doesn't take into account the If's logical test (the date)...

2/10 Joe
2/10 Bill
2/10 Sue
2/10 Joe
2/11 Joe
2/11 Bill
2/11 Ann

The formula will assign each Joe entry a .33333 value since there are 3 of them. It will assign each Bill a .5 value since there are 2 of them; Sue and Ann will each be assigned a value of 1 since there are one of each.

But the final result only sums the assigned values if the logical test (the date criteria) is true. This explains the decimal numbers...

0.33333 + 0.5 + 1 + 0.33333 = 2.166

Sorry I wasn't more help.


Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[GlennUK]

Concatenate the date and name, say in column F, and use an array formula ( using Ctrl-Shift-Enter ) of:

=SUM(IF(A2:A1000=Sheet2!G2,1/(COUNTIF(F2:F1000,F2:F1000)),0))

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Rofeu]

Hello,

just wondering why a new thread was started while the old one was still active??

Did you look at the formula I posted there?

It will allow you to get a count of the unique values without the need for a concatenation in a new column, it can be done within the formula. It will even allow multiple conditions if need be.

Cheers,

Roel

 

[GlennUK]

Yeah Roel, I thought this looked like a duplicate thread too. Looks like dr772 isn't trying the solutions either.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[dr772]

I have tried all suggestions and was left with nothing or the decimal that I explained above.
I will keep trying..thanks