Excel formula for unique count but based on condition

try Power Query

Please Login or Register  to view this content.

or with formula:

=SUM(IF(Table1[Email]<>"N",1/(COUNTIFS(Table1[Email],"<>N",Table1[Last Name],Table1[Last Name])),0))

Originally Posted by sandy666

try Power Query

Please Login or Register  to view this content.

yes, that'd work thanks. but i should've posted earlier. my plan is to eventually take this data to google sheets. so i was interested in a formula.

Cell B16 formula

Formula:

Please Login or Register  to view this content.

Cell C14 formula , Drag down

Formula:

Please Login or Register  to view this content.

Originally Posted by wk9128

Cell B16 formula

Formula:

Please Login or Register  to view this content.

The B16 formula seems to work, thats what I want. But still there is one issue. The formula is hardcoded. The column references $C$2:$C$8 are still fine, because I can always insert rows in between instead of at the end. This way those references auto-adjust the rows.

But the main problem is the row reference at the end of the formula. Is there a way to make it dynamic? ROW($1:$7)
I tried inserting a new row in the data and the formula doesnt work, needs adjusting.

ANS.POST#7 Please expand the source data range, maybe it can solve your problem

@amitdi You're Welcome. Glad to help . Thank You for the feedback and rep.

Originally Posted by wk9128

ANS.POST#7 Please expand the source data range, maybe it can solve your problem

@amitdi You're Welcome. Glad to help . Thank You for the feedback and rep.

Hi,
This is solved. Thanks.

But I found an interesting issue, which I also resolved myself. I'll try to explain in case you or anybody else reading this thread later might be interested.

When I used your formula on my entire data, I was getting the answer 64, but my manual steps were giving the answer 67. Now 67 is the correct answer. On debugging, the issue was in unclear requirement conditions.

You see, one of the family has following rows

Family Name/Email/Email Address
Smith//
Smith//test@test.ca

Now your formula does not count Smith because the match fails. This is because the email populated is not the 1st row when it encounters Smith. But we need to count Smith, because they have an email address row.

I started building my own formula in Google sheets and was able to come up with this:

Please Login or Register  to view this content.

Since COUNTUNIQUE function does not exist in MS Excel and I dont know of an alternative, I cannot provide an alternative. But thats ok, as I anyways was planning to migrate this to Google sheets.

Thank you for all the posts as all of the above posts helped me build towards this solution.

Hi @amitdi but your profile says OFFICE 2016 if google sheet sorry i'm out

Originally Posted by wk9128

Hi @amitdi but your profile says OFFICE 2016 if google sheet sorry i'm out

Yes, Excel 2016 is what I've installed on my laptop. But its fine, as I found what I was looking for. Thanks for your inputs though, because your input gave me breakthrough.