Nested (?) formula/function to count unique entries

[hambly]

Excel_forum_query.xls

I am trying to get a formula to count unique entires based on the date entered.

Sheet "DAY TO DAY" will have the data that will list the date in column A, and "Event Location" in column C.

On the "Daily Business Report" in column "M" I would like it to count the # of unique entries based off the date for that particular row.

For the attached sample, M6 on the Daily Business report would count all unique entries in C:C of the DAY TO DAY sheet, that have the same date in DAY TO DAY A:A

It's easier to look at the attached than to explain!

I've seen Frequency samples, but am not sure if they are what I need....

[Debraj Roy]

In Sheet 2.. C6..
use array formula as..

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

PS.. EDIT
OOPS.. just notice.. MAC Excel.. Crossing finger.. not sure will work or not..

[AlKey]

Hi you can use Frequency function

=SUM(IF(FREQUENCY('DAY TO DAY'!A4:A20,'DAY TO DAY'!A4:A20)>0,1))

[hambly]

Thanks both. AlKey, I need to count unique values in column C of DAY TO DAY based off the A column.

Debraj, yours worked just fine, however I'm looking for it to look at the entire column (DAY TO DAY A:A and DAY TO DAY C:C) Will the array still work changing to A:A / C:C? It seemed to slow down and not give a value

[Ace_XL]

In M6

=SUMPRODUCT(('DAY TO DAY'!$A$4:$A$20=A6)/COUNTIF('DAY TO DAY'!$C$4:$C$20,'DAY TO DAY'!$C$4:$C$20&""))

copy down

[AlKey]

Here is in column C

=SUM(IF(FREQUENCY(MATCH('DAY TO DAY'!C4:C20,'DAY TO DAY'!C4:C20,0),MATCH('DAY TO DAY'!C4:C20,'DAY TO DAY'!C4:C20,0))>0,1))

And this is unique values in for M6

Just copy and paste it in M6

=INDEX('DAY TO DAY'!$C$4:$C$20,MATCH(0,INDEX(COUNTIF($M$5:M5,'DAY TO DAY'!$C$4:$C$20),0,0),0))

[hambly]

Thanks again.

It looks like the below formula does what I need... I just changed for more rows..

Please Login or Register  to view this content.

[hambly]

Excel_forum_query2.xlsOne issue I'm running into... The DAY TO DAY sheet is an ever growing list, so I'd need the formula to look at the whole sheet, not just the a certain range...

When I do this, I seem to get a result that is fractional... See attached...

[Haseeb Avarakkan]

Hambly,

Are you sure that works? In C4 Day to day tab, just change the value to "Place 1" (without quote) & see the answer.

Here is one generic way to count

Array Formula, with CTRL+SHIFT+ENTER

=SUM(IF(FREQUENCY(IF('DAY TO DAY'!A$4:A$20=A6,IF('DAY TO DAY'!C$4:C$20<>"",MATCH('DAY TO DAY'!C$4:C$20,'DAY TO DAY'!C$4:C$20,0))),ROW('DAY TO DAY'!C$4:C$20)-ROW('DAY TO DAY'!C$4)+1),1))

With Just ENTER

=SUMPRODUCT(('DAY TO DAY'!A$4:A$20=A6)*('DAY TO DAY'!C$4:C$20<>"")*(MATCH('DAY TO DAY'!A$4:A$20&"_"&'DAY TO DAY'!C$4:C$20,'DAY TO DAY'!A$4:A$20&"_"&'DAY TO DAY'!C$4:C$20,0)=ROW('DAY TO DAY'!A$4:A$20)-ROW('DAY TO DAY'!A$4)+1))

[hambly]

Thanks Haseeb.

Yes, the Array formula seems to work well. Again, I'm wanting the formula to look at column A and C rather than just a certain range (A4:A20)... Will the array work looking at the whole column? It seems that involves a lot of calculating on Excels part, slowing it down? I could guess how many rows I will use.... I tried it with a range of A4:A8000 and that seemed to work fine..

[Haseeb Avarakkan]

Hambly, you can use dynamic range in the formula. SO whenever add/delete entries in day to day tab, range will be adjusted. This way formula always looking only in used cells.

I used 4 named ranges in your file:

Name: FirstRow
Refers to: =ROW('DAY TO DAY'!$A$2)

Name: LastRow
Refers to: =MATCH(100^100,'DAY TO DAY'!$A:$A)

Name: Dates
Refers to: =INDEX('DAY TO DAY'!$A:$A,FirstRow):INDEX('DAY TO DAY'!$A:$A,LastRow)

Name: EventLocation
Refers to: =INDEX('DAY TO DAY'!$C:$C,FirstRow):INDEX('DAY TO DAY'!$C:$C,LastRow)

Then used in M6,

=SUM(IF(FREQUENCY(IF(Dates=A6,IF(EventLocation<>"",MATCH(EventLocation,EventLocation,0))),ROW(Dates)-FirstRow+1),1))

See attached file.

[hambly]

Amazing.... Thanks Haseeb... Seems to do the job perfectly....

I'll test a little more, then mark as solved...

Thanks again!

[hambly]

DELETE POST - My mistake.