Count If with multiple criteria and unique values

[dixon1983]

Hi,
I dont know if this is possible or not but thought I'd ask anyway.

I have a sheet of data and want to run a count on the values in Column A with the following criteria:

- If the value in column B is repeated then the value in column A can only be counted once...
- Column C must equal 'M'.


Example:
A.....B.....C
1.....42....M
1.....42....M
2.....41....A
2.....41....M
1.....56....M


In this example 1 would be counted 2 times, and 2 would be counted once.

I have about 15 sheets of data like this and would like a master sheet to look into each sheet and give me a count of each so id prefer to not use the advanced filter => unique variables option if possible.

Any help would be greatly appreciated.

Dixon.

 

[Aladin Akyurek]

I would hope to get 4 in my unduplicated count of names (as long as jonathan and jon are actually 2 different people).

Jon and Jonathan are different people. So what is the count? If it is 4, that means we have 4 unique/distinct names...

 

[Rriot]

Jon and Jonathan are different people. So what is the count? If it is 4, that means we have 4 unique/distinct names...

Yes exactly. And then from there I'll need to count how many of those folks are from each city. And then another count of how many of those folks are each marital status.

This is the code you may have already given me, and I can double check. I apologize for my slow process and appreciate your patience with me.

 

[Aladin Akyurek]

Yes exactly. And then from there I'll need to count how many of those folks are from each city. And then another count of how many of those folks are each marital status.

This is the code you may have already given me, and I can double check. I apologize for my slow process and appreciate your patience with me.

Yes, the formulas I provided cover exactly these specifications.

 

[Rriot]

Yes, the formulas I provided cover exactly these specifications.

Yes you are right (of course), I think I was doing something wrong the first time I tried. Thank you so much!!

 

[Aladin Akyurek]

Yes you are right (of course), I think I was doing something wrong the first time I tried. Thank you so much!!

You are welcome. And thanks for the update.

 

[Joshua Bons]

This thread has helped many people and I hope I will be as fortunate. My problem is similar to some earlier in the thread but I was unable to adapt any formulas to work with my spreadsheet.

I have a report file designed to track the effectiveness of advertising based on the number of unique people who call in on a specific phone number after an ad. In one worksheet I have a list of every inbound phone call and the DID it called in on. In another worksheet I have a summary that tracks total phone calls that come in on ad phone number and I would like it to also track the number of phone calls from unique phone numbers so we have a better idea of how many people called in versus how many times a call came in.

The formula I use to track total calls is:

=COUNTIF('01C'!$K$2:$K$30000, "="&B2)

01C is the worksheet where the phone data is copied, column K tells me which ad generated the call which is then counted if it matches B2 on the summary sheet.

The phone number of the person calling in is in column F of the 01C worksheet and this is what I am trying to get a unique count on. I can't use some of the simpler solutions I've seen mentioned because if the same phone number called in from two different sources I still want it counted once for each source.

To summarize the desired outcome: If 50 phone calls come in from a specific source but 30 of them were repeat calls from people who already called the desired output would be 20 in the summary worksheet for that source.

Please let me know if you have any questions.

 

[Aladin Akyurek]

@Joshua Bons

Sounds like...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-('01C'!$F$2:$F$30000=""),IF('01C'!$K$2:$K$30000=$B2,
     MATCH('01C'!$F$2:$F$30000,'01C'!$F$2:$F$30000,0))),ROW('01C'!$F$2:$F$30000)-ROW('01C'!$F$2)+1),1))

If you define Ivec by means of FORMULAS | Name Manager as referring to:

=ROW('01C'!$F$2:$F$30000)-ROW('01C'!$F$2)+1

the formula for unique count can be a tad faster:

=SUM(IF(FREQUENCY(IF(1-('01C'!$F$2:$F$30000=""),IF('01C'!$K$2:$K$30000=$B2,
     MATCH('01C'!$F$2:$F$30000,'01C'!$F$2:$F$30000,0))),Ivec),1))

If the foregoing is not delivering what you expected, please try to post a tiny sample from F and K of 01C.

 

[Joshua Bons]

@Aladin Akyurek

Your first solution is exactly what I needed. Thank you very much.

 

[Aladin Akyurek]

@Aladin Akyurek

Your first solution is exactly what I needed. Thank you very much.

You are welcome.

 

[yaniskyr]

The method that evolved in this thread is amazing. May I bump the thread with a question though?

My personalised formula works ok until I add the wildcard in the last IF - $F5&"*". During trial and error, replacing that with $F5 and adjusting F5 to a precise value, it works. Any ideas??

=SUM(IF(FREQUENCY(
IF(1-(Export!$B2:$B40000=""),
IF(Export!$M2:$M40000>=G$2,
IF(Export!$M2:$M40000<=J$2,
IF(Export!$D2:$D40000=$B5,
IF(Export!$F2:$F40000=$D5,
[B]IF(Export!$K2:$K40000=$F5&"*"[/B],
MATCH(Export!B2:B40000,Export!B2:B40000,0))))))),ROW(Export!B2:B40000)-ROW(Export!B2)+1),1))

Edit: just broke the lines to be easier readable on the forum.