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.

 

[Eric W]

Oh, wow, a cake sounds nice!!

As far as learning Excel, I'm largely self taught. I have been a programmer for many years, and a lot of the principles I learned in class and on the job carry over. But mostly it's from digging in and experimenting. Actually, this is a great example. Go through my formula and Aladin's and try to understand how they work, and what the differences are. Aladin typically writes formulas that work for any data, while I might write a simpler formula if I believe the data won't have any specific issues. In this case I made 2 assumptions, that you wouldn't have a value in B if A was empty, and that since column A has companies and B has locations, you wouldn't need the "|". Which brings us back to Aladin's question: do you always need the "|"? Is it needed in this case? Should you put it in as a basic rule? These are some of the high level questions you might ask when designing a formula.

Just keep plugging away. The more you do, the more intuitive it becomes. If you have questions, feel free to come back and ask!

 

[Rriot]

Hello,

This thread is amazing! I'm not sure if you've answered my excel question yet, (I read through about 20 pages before I decided to just ask). I'm trying to count unique values in column B (unduplicated count of individuals, so if someone's there twice they only get counted once) and then a separate count of how many of those unduplicated folks are from Jersey City, and then how many of those unduplicated folks are single. So basically, is there a way to generate a count of terms from columns C and D that solely reflect off the unduplicated/unique values in B? THANKS!

A B C D
[TABLE="width: 901"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]03/03/16 [/TD]
[TD]Sam. H. [/TD]
[TD]Jersey City[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Steve. M. [/TD]
[TD]Jersey City[/TD]
[TD]Married[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Harold. R. [/TD]
[TD]Kearny[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Harold. R. [/TD]
[TD]Kearny[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/04/16 [/TD]
[TD]Sherry. S.[/TD]
[TD]Jersey City[/TD]
[TD]Married[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Calvin. B.[/TD]
[TD]Jersey City[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Calvin. B.[/TD]
[TD]Jersey City[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Nancy. R.[/TD]
[TD]Jersey City[/TD]
[TD]Married[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Nancy. R.[/TD]
[TD]Jersey City[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/07/16 [/TD]
[TD]Sherry S.[/TD]
[TD]Jersey City[/TD]
[TD]Married[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Kathy. H. [/TD]
[TD]Jersey City[/TD]
[TD]Married[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rriot]

Ugh, my report pasted differently. Maybe this helps - without columns listed ABCD, but A is the date, B is the name, C is the city, D is single/married. You probably could've figured that out but I wanted to be clear. THANKS again!


[TABLE="width: 901"]
<tbody>[TR]
[TD="align: right"]03/03/16[/TD]
[TD]Sam. H.[/TD]
[TD]Jersey City[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Steve. M.[/TD]
[TD]Jersey City[/TD]
[TD]Married[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Harold. R.[/TD]
[TD]Kearny[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Harold. R.[/TD]
[TD]Kearny[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/04/16[/TD]
[TD]Sherry. S.[/TD]
[TD]Jersey City[/TD]
[TD]Married[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Calvin. B.[/TD]
[TD]Jersey City[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Calvin. B.[/TD]
[TD]Jersey City[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Nancy. R.[/TD]
[TD]Jersey City[/TD]
[TD]Married[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Nancy. R.[/TD]
[TD]Jersey City[/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/07/16[/TD]
[TD]Sherry S.[/TD]
[TD]Jersey City[/TD]
[TD]Married[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Kathy. H.[/TD]
[TD]Jersey City[/TD]
[TD]Married[/TD]
[/TR]
</tbody>[/TABLE]
[/QUOTE]

 

[Aladin Akyurek]

@Rriot

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td]

3/3/2016​

[/td][td] Sam. H.[/td][td] Jersey City[/td][td] Single[/td][td] [/td][/tr]
[tr][td]

2​

[/td][td] [/td][td] Steve. M.[/td][td] Jersey City[/td][td] Married[/td][td] unique individuals[/td][/tr]
[tr][td]

3​

[/td][td] [/td][td] Harold. R.[/td][td] Kearny[/td][td] Single[/td][td] 8[/td][/tr]
[tr][td]

4​

[/td][td] [/td][td] Harold. R.[/td][td] Kearny[/td][td] Single[/td][td] Jersey City[/td][/tr]
[tr][td]

5​

[/td][td]

3/4/2016​

[/td][td] Sherry. S.[/td][td] Jersey City[/td][td] Married[/td][td] 7[/td][/tr]
[tr][td]

6​

[/td][td] [/td][td] Calvin. B.[/td][td] Jersey City[/td][td] Single[/td][td] Single[/td][/tr]
[tr][td]

7​

[/td][td] [/td][td] Calvin. B.[/td][td] Jersey City[/td][td] Single[/td][td] 4[/td][/tr]
[tr][td]

8​

[/td][td] [/td][td] Nancy. R.[/td][td] Jersey City[/td][td] Married[/td][td] [/td][/tr]
[tr][td]

9​

[/td][td] [/td][td] Nancy. R.[/td][td] Jersey City[/td][td] Single[/td][td] [/td][/tr]
[tr][td]

10​

[/td][td]

3/7/2016​

[/td][td] Sherry S.[/td][td] Jersey City[/td][td] Married[/td][td] [/td][/tr]
[tr][td]

11​

[/td][td] [/td][td] Kathy. H.[/td][td] Jersey City[/td][td] Married[/td][td][/td][/tr]
[/table]

In E3 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($B$1:$B$11=""),MATCH($B$1:$B$11,$B$1:$B$11,0)),
    ROW($B$1:$B$11)-ROW($B$1)+1),1))

In E5 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($B$1:$B$11=""),IF($C$1:$C$11=E4,MATCH($B$1:$B$11,$B$1:$B$11,0))),
    ROW($B$1:$B$11)-ROW($B$1)+1),1))

In E7 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($B$1:$B$11=""),IF($D$1:$D$11=E6,MATCH($B$1:$B$11,$B$1:$B$11,0))),
    ROW($B$1:$B$11)-ROW($B$1)+1),1))

 

[tbecker3]

Hi Aladin. This needs to go to Rriot, I think. Can you resend to them.

 

[Aladin Akyurek]

Hi Aladin. This needs to go to Rriot, I think. Can you resend to them.

Post #314 addresses Rriot's sample and his/her questions and tagged with @Rriot. So I guess the post 314 is okay.

 

[Rriot]

Thank you so much!! I'll try this out and if any issues arrive, I'll bug you again. This message board is amazing! =)

 

[Rriot]

Hi Aladin,

I think you're super close at giving me the right code, but the counts need to include the duplicates once, otherwise I'm leaving them out of the tally. For example, if Calvin is listed twice, I need to count him once, including his correlating info, in my totals of individual/city/marital status and I believe the codes above discount the count of all duplicates entirely, instead of counting the duplicate solely once. I'm having a hard time communicating my needs so I completely understand if this is confusing. When I tried these codes in my actual report from lines 1:255, it leaves out the duplicates completely instead of counting them once. Let me know if what I'm asking for is possible, or if I'm misinterpreting the codes and they do in fact already do what I'm asking. THANKS AGAIN!

 

[Aladin Akyurek]

Hi Aladin,

I think you're super close at giving me the right code, but the counts need to include the duplicates once, otherwise I'm leaving them out of the tally. For example, if Calvin is listed twice, I need to count him once, including his correlating info, in my totals of individual/city/marital status and I believe the codes above discount the count of all duplicates entirely, instead of counting the duplicate solely once. I'm having a hard time communicating my needs so I completely understand if this is confusing. When I tried these codes in my actual report from lines 1:255, it leaves out the duplicates completely instead of counting them once. Let me know if what I'm asking for is possible, or if I'm misinterpreting the codes and they do in fact already do what I'm asking. THANKS AGAIN!

jon
jonathan
jon
damon
damon
karl
damon

What is the count you expect for the above data?

 

[Rriot]

jon
jonathan
jon
damon
damon
karl
damon

What is the count you expect for the above data?

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