Sum uniques based on a condition

[dr772]

I have a spreadsheet that I need to count uniques during a payperiod see below

A F
2/10 2
2/10 2
2/10 10
2/24 3
2/24 5
2/24 5
2/24 1
I am able to count the uniques with the following
=SUM(IF(FREQUENCY(IF(LEN(F$2:$F$8000)>0,MATCH($F$2:$F$8000,$F$2:$F$8000,0),""),IF(LEN($F$2:$F$8000)>0,MATCH($F$2:$F$8000,$F$2:$F$8000,0),""))>0,1))
but I need to incorporate column A as a condition
so I will get
2/10 = 2
2/24 = 3

 

[bluedragon2]

Take a look at pivot tables

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[dr772]

I need a formula...I don'think pivot tables will do what I need
thanks for the thought

 

[Scott02]

I'm not clear on what you're looking for in your example, but if you need to count something, pivot tables do work wonders if you haven't tried it yet.

good luck

Scott

 

[GlennUK]

How about an array formula ( entered using Ctrl-Shift-Enter instead of Enter ):

=SUM(IF(A2:A8=E2,1/(COUNTIF(B2:B8,B2:B8)),0))

where E2 contains the date for testing, for example 2/10.

Cheers, Glenn.

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

 

[dr772]

Glenn,
I tried it, it looks as if it should work but I am not getting the correct results. any other thoughts

I am determined to get a formula that works
Thanks
Diane

 

[GlennUK]

It works for me.

Have you checked that the referenced ranges are correct? Have you entered a value into E2 ( or whatever cell you are using for the control date )?

What is the exact formula that you are using?

Did you enter this as an array formula ( this is very important )?

Cheers, Glenn.

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

 

[dr772]

Yes contains the date 2/10 and is entered as array (cntrl shift enter)I get a number not an error but it is not the right number. uhhh...

 

[GlennUK]

What's the data? ( and cell locations please )

What's the formula?

What's the result?

I should be able to debug it on my Excel session from that info.

Cheers, Glenn.

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

 

[xlhelp]

Hey, Diane.

That GlenUK is a smart cookie. His formula should work.

Try this as variation in one of your blank columns. It's the same as Glen's with a slight change.

=IF(A2=A1,"",SUM(IF(A2:A9=A2,1/(COUNTIF(B2:B9,B2:B9)),0)))

Member- AAAA Association Against Acronym Abusers

 

[dr772]

Glenn,

(A) (B) (C)
Payperiod
ending Units NAME (row 2)
2/10/2007 sicu o'dowd
2/10/2007 picu mayorga
2/10/2007 nscu bowen
2/10/2007 ctu karides
2/10/2007 5 monti hills
2/10/2007 4 monti golin
2/10/2007 4 cohen ingisani
2/10/2007 4 cohen zeitchek
2/10/2007 4 cohen gibbard
2/10/2007 4 cohen timpone
2/10/2007 3 Tower whelan
2/10/2007 3 DSU schwarz
2/10/2007 3 Cohen pallas
2/10/2007 2 Cohen potaphshyn
2/10/2007 2 Cohen nadim
2/10/2007 3 Tower mayorga
2/10/2007 SICU o'dowd
2/10/2007 MICU setton
2/10/2007 3 Tower Loskofsky
2/24/2007 6 Monti hom
2/24/2007 4 Cohen ingrisani
2/24/2007 4 Cohen hom
---------------------------------
Results:
2/10/2007 2/24/2007
# Patients (b2) (b3)


I hope this makes sense ()denotes cell location
Thanks

 

[GlennUK]

Ah yes, I see the problem now. To get the results you want I think you need a dummy formula in column D of =A3&B3 copied down, and then use the same formula as before, except that the counting should point at the new column instead.

=SUM(IF(A3:A25=B28,1/(COUNTIF(D3:D25,D3:D25)),0))

entered using Ctrl-Shift-Enter.

Cheers, Glenn.

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

 

[dr772]

still not working...what does that dummy column do...I gave you an abreviated version of my data there are a few columns between payperiod and name....
This is frustrating

Also xhlep I tried your and it gave me the same value as Glenn's...I will keep trying
THanks

 

[xlhelp]

Basically, you have data in 3 columns. A2:C23

What Glenn is suggesting is you create a dummy columns to combine data in B & C.

Formula in D2 would
=B2&C2
Then in E2, if you use formula is suggested (type it in or Paste it) and press CTRL/Shift/Enter.

=IF(A2=A1,"",SUM(IF(A2:A23=A2,1/(COUNTIF(D223,D223)),0)))

Copy it down all the way.

It should give you 18 for the 1oth and 3 for the 24th.

Member- AAAA Association Against Acronym Abusers

 

[GlennUK]

Is xlhelp right? It looks right to me. A thought occurs to me ... is this real(ish) data? Are you relying on a combination of unit & surname to identify each patient? If so, won't there be cases of Smith in the same unit at the same time?

Cheers, Glenn.

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

 

[Rofeu]

Hi,

try this as an array formula with A1 containing the date for which you want the unique count:

{=SUMPRODUCT(--(MATCH(B1:B100&C1:C100&D1:D100,B1:B100&C1:C100&D1:D100,FALSE)=ROW(B1:B100)),--(A1=B1:B100))}

Cheers,

Roel