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.

 

[KamCheng]

@KamCheng

[TABLE="class: grid, width: 456"]
<tbody>[TR]
[TD]Count of Unique Accounts[/TD]
[TD]201701[/TD]
[TD]201702[/TD]
[TD]201703[/TD]
[TD]201704[/TD]
[/TR]
[TR]
[TD]Inbound[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Outbound[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Door to Door[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

In B2 control+shift+enter, not just enter, copy across, and down:

=SUM(IF(FREQUENCY(IF(Channel=$A2,IF(CCYYMM=B$1,Account)),Account),1))

Thank you! This worked perfectly for what I was trying to accomplish.

 

[Aladin Akyurek]

Thank you! This worked perfectly for what I was trying to accomplish.

You are welcome.

 

[Zedrick13]

E2: Mr.X
E3: Mr.Y

F2, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$9<>"",IF($B$2:$B$9=E2, 
    MATCH("~"&$A$2:$A$9,$A$2:$A$9&"",0))),
       ROW($A$2:$A$9)-ROW($A$2)+1),1))

How do I do the same if I have 4 columns instead? Let's say it looks like this:

"Letters" =Column
"_" =separates data per column

Table 1
ColA_ColB_ColC_ColD
Row2-Accounting_North_Regular_blue
Row3-HRDept_East_Temporary_blue
Row4-Accounting_East_Regular_green
Row5-Merchandising_West_Regular_blue
Row6-Utility_South_Temporary_red
Row7-Security_North_Regular_blue
Row8-Security_North_Regular_blue
Row9-Merchandising_West_Regular_blue
Row10-Utility_South_Temporary_red
Row11-Security_North_Regular_blue
Row12-Accounting_North_Regular_blue
Row13-HRDept_East_Temporary_blue
Row14-HRDept_East_Regular_blue
Row15-Security_North_Regular_green
Row16-Accounting_North_Regular_blue
Row17-Security_North_Regular_blue
Row18-Accounting_North_Regular_blue


What I want to know is the number of colors (unique values; disregarding duplicate data) are there available that are under "Regular" that are also "North" that are under "Security".

Thanks!

 

[Aladin Akyurek]

How do I do the same if I have 4 columns instead? […]

Book1
	A	B	C	D	E
1
2	Accounting	North	Regular	blue	security
3	HRDept	East	Temporary	blue	north
4	Accounting	East	Regular	green	regular
5	Merchandising	West	Regular	blue	2
6	Utility	South	Temporary	red
7	Security	North	Regular	blue
8	Security	North	Regular	blue
9	Merchandising	West	Regular	blue
10	Utility	South	Temporary	red
11	Security	North	Regular	blue
12	Accounting	North	Regular	blue
13	HRDept	East	Temporary	blue
14	HRDept	East	Regular	blue
15	Security	North	Regular	green
16	Accounting	North	Regular	blue
17	Security	North	Regular	blue
18	Accounting	North	Regular	blue
19
Sheet1

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

=SUM(IF(FREQUENCY(IF($D$2:$D$18<>"",IF($A$2:$A$18=E2,IF($B$2:$B$18=E3,IF($C$2:$C$18=E4,MATCH("~"&$D$2:$D$18,$D$2:$D$18&"",0))))),
ROW($D$2:$D$18)-ROW($D$2)+1),1))

 

[Zedrick13]

	A	B	C	D	E
Accounting	North	Regular	blue	security
HRDept	East	Temporary	blue	north
Accounting	East	Regular	green	regular
Merchandising	West	Regular	blue
Utility	South	Temporary	red
Security	North	Regular	blue
Security	North	Regular	blue
Merchandising	West	Regular	blue
Utility	South	Temporary	red
Security	North	Regular	blue
Accounting	North	Regular	blue
HRDept	East	Temporary	blue
HRDept	East	Regular	blue
Security	North	Regular	green
Accounting	North	Regular	blue
Security	North	Regular	blue
Accounting	North	Regular	blue

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

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

=SUM(IF(FREQUENCY(IF($D$2:$D$18<>"",IF($A$2:$A$18=E2,IF($B$2:$B$18=E3,IF($C$2:$C$18=E4,MATCH("~"&$D$2:$D$18,$D$2:$D$18&"",0))))),
ROW($D$2:$D$18)-ROW($D$2)+1),1))

Hi! Thank you very much for getting back to me instantly. I really appreciate it.

 

[Aladin Akyurek]

Hi! Thank you very much for getting back to me instantly. I really appreciate it.

You are welcome.