Unique Value with multiple criteria form other columns

nooracam · Apr 28, 2011

Hi All,

This is my first post here. I will do my best to explain my problem. Usually I don't have issues figuring out what I need form others' posts, but I'm really stuck here. I've been trying to figure this out for two full working days now and haven't solved it.

I am using a data dump file that feeds formulas on a topline sheet for a report. The data dump could have >40000 rows, and the number of rows may differ month to month. No blanks in the table, and all values are numeric. I'm using excel 2010.

Small sample of data:

A....B....C
1....7....1
1....2....0
1....9....1
4....9....0
4....9....1
4....7....1
12...9....0
12...66...0
12...66...0

Column A is customer number, column B is brands, and column C is a sales indicator. (1 means case sales>0, 0 means customer has not purchased that brand)

I'd like to count all the customers when column b = 7,9, or 66 and column c>0. The above data would return '2.'

Below is a formula I put together based on my readings of other threads, but it's returning the wrong number for some reason, and I can't wrap my head around it.

=SUM(IF(FREQUENCY(IF(OR('key acct'!A:A<>"",'key acct'!B:B=7,'key acct'!B:B=9,'key acct'!B:B=66),IF('key acct'!C:C>0,'key acct'!A:A)),'key acct'!A:A)>0,1))

Thanks in advance for the help.

Cheers!

nooracam · Jun 1, 2011

This has turned out to be quite an interesting little formula.

The "on " and "off" values to filter on are in column L.

That's my fault, Aladin, for not pointing that out.

Just to rehash what I'm after, I want to count unique values in column E where in the same row:
column K <>0
column H = 7,9 or 66
column Q = 1
column L = "off"

Below is my formula (right now it returns '0'):

=SUM(IF(FREQUENCY(
IF('key acct'!K2:K40000<>"",
IF(ISTEXT(MATCH('key acct dump'!L2:L40000,{"off"},0)),
IF(ISNUMBER(MATCH('key acct'!H2:H40000,{7,9,66},0)),
IF(1-('key acct'!Q2:Q40000=1),
MATCH('key acct'!K2:K40000,'key acct'!K2:K40000))))),
ROW('key acct'!K2:K40000)-ROW('key acct'!K2)+1),1))

nooracam · Jun 1, 2011

Correction:

This has turned out to be quite an interesting little formula.

The "on " and "off" values to filter on are in column L.

That's my fault, Aladin, for not pointing that out.

Just to rehash what I'm after, I want to count unique values in column K where in the same row:
column K <>0
column H = 7,9 or 66
column Q = 1
column L = "off"

Below is my formula (right now it returns '0'):

=SUM(IF(FREQUENCY(
IF('key acct'!K2:K40000<>"",
IF(ISTEXT(MATCH('key acct dump'!L2:L40000,{"off"},0)),
IF(ISNUMBER(MATCH('key acct'!H2:H40000,{7,9,66},0)),
IF(1-('key acct'!Q2:Q40000=1),
MATCH('key acct'!K2:K40000,'key acct'!K2:K40000))))),
ROW('key acct'!K2:K40000)-ROW('key acct'!K2)+1),1))

Aladin Akyurek · Jun 1, 2011

nooracam said:
Correction:

This has turned out to be quite an interesting little formula.

The "on " and "off" values to filter on are in column L.

That's my fault, Aladin, for not pointing that out.

Just to rehash what I'm after, I want to count unique values in column K where in the same row:
column K <>0
column H = 7,9 or 66
column Q = 1
column L = "off"

Below is my formula (right now it returns '0'):

=SUM(IF(FREQUENCY(
IF('key acct'!K2:K40000<>"",
IF(ISTEXT(MATCH('key acct dump'!L2:L40000,{"off"},0)),
IF(ISNUMBER(MATCH('key acct'!H2:H40000,{7,9,66},0)),
IF(1-('key acct'!Q2:Q40000=1),
MATCH('key acct'!K2:K40000,'key acct'!K2:K40000))))),
ROW('key acct'!K2:K40000)-ROW('key acct'!K2)+1),1))

Almost there...
Control+shift+enter, not just enter:

Code:

=SUM(IF(FREQUENCY(
      IF('key acct'!K2:K40000<>"",
      IF('key acct dump'!L2:L40000="off",
      IF(ISNUMBER(MATCH('key acct'!H2:H40000,{7,9,66},0)),
      IF('key acct'!Q2:Q40000=1,
        MATCH('key acct'!K2:K40000,'key acct'!K2:K40000,0))))), 
          ROW('key acct'!K2:K40000)-ROW('key acct'!K2)+1),1))

Performance can be an issue though.

T. Valko · Jun 1, 2011

nooracam said:
Correction:

This has turned out to be quite an interesting little formula.

The "on " and "off" values to filter on are in column L.

That's my fault, Aladin, for not pointing that out.

Just to rehash what I'm after, I want to count unique values in column K where in the same row:
column K <>0
column H = 7,9 or 66
column Q = 1
column L = "off"

Below is my formula (right now it returns '0'):

=SUM(IF(FREQUENCY(
IF('key acct'!K2:K40000<>"",
IF(ISTEXT(MATCH('key acct dump'!L2:L40000,{"off"},0)),
IF(ISNUMBER(MATCH('key acct'!H2:H40000,{7,9,66},0)),
IF(1-('key acct'!Q2:Q40000=1),
MATCH('key acct'!K2:K40000,'key acct'!K2:K40000))))),
ROW('key acct'!K2:K40000)-ROW('key acct'!K2)+1),1))

There's a big difference between column K <>0and IF('key acct'!K2:K40000<>"".

So which is the correct criteria to use?

Also, this is contradictory:

column Q = 1

And

IF(1-('key acct'!Q2:Q40000=1)

nooracam · Jun 2, 2011

The below works:

=SUM(IF(FREQUENCY(
IF('key acct'!K2:K50000<>"",
IF('key acct'!L2:L50000="ON ",
IF(ISNUMBER(MATCH('key acct'!H2:H50000,{7,9,66},0)),
IF(1-('key acct'!Q2:Q50000=0),
MATCH('key acct'!K2:K50000,'key acct'!K2:K50000))))),
ROW('key acct'!K2:K50000)-ROW('key acct'!K2)+1),1))

Thanks again all! Learning a little bit more each day!

Aladin Akyurek · Jun 2, 2011

nooracam said:
The below works:

=SUM(IF(FREQUENCY(
IF('key acct'!K2:K50000<>"",
IF('key acct'!L2:L50000="ON ",
IF(ISNUMBER(MATCH('key acct'!H2:H50000,{7,9,66},0)),
IF(1-('key acct'!Q2:Q50000=0),
MATCH('key acct'!K2:K50000,'key acct'!K2:K50000))))),
ROW('key acct'!K2:K50000)-ROW('key acct'!K2)+1),1))

Thanks again all! Learning a little bit more each day!

Great. Thanks for providing feedback.

T. Valko · Jun 2, 2011

nooracam said:
The below works:

=SUM(IF(FREQUENCY(
IF('key acct'!K2:K50000<>"",
IF('key acct'!L2:L50000="ON ",
IF(ISNUMBER(MATCH('key acct'!H2:H50000,{7,9,66},0)),
IF(1-('key acct'!Q2:Q50000=0),
MATCH('key acct'!K2:K50000,'key acct'!K2:K50000))))),
ROW('key acct'!K2:K50000)-ROW('key acct'!K2)+1),1))

Thanks again all! Learning a little bit more each day!

Good deal. Thanks for the feedback!

T. Valko · Jun 2, 2011

nooracam said:
The below works:

=SUM(IF(FREQUENCY(
IF('key acct'!K2:K50000<>"",
IF('key acct'!L2:L50000="ON ",
IF(ISNUMBER(MATCH('key acct'!H2:H50000,{7,9,66},0)),
IF(1-('key acct'!Q2:Q50000=0),
MATCH('key acct'!K2:K50000,'key acct'!K2:K50000))))),
ROW('key acct'!K2:K50000)-ROW('key acct'!K2)+1),1))

Thanks again all! Learning a little bit more each day!

Just one point...

This expression:

1-('key acct'!Q2:Q50000=0)

Is obfuscated and convoluted!

It can be more simply expressed as:

'key acct'!Q2:Q50000<>0

Why use reverse logic that adds an extra unnecessary processing cycle to calculate when a straightforward comparison will do?

Big Bruiser · Jun 7, 2011

Guys, I am having a similar problem. I tried modifying your formulas (as arrays), but I am still having trouble. I need to count the number of unique emails (column C) if the MQN (column J) equals Q6. Will you help me modify your formula to fit my data?

<TABLE style="WIDTH: 1032pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1376 border=0><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5083" width=139><COL style="WIDTH: 191pt; mso-width-source: userset; mso-width-alt: 9289" width=254><COL style="WIDTH: 163pt; mso-width-source: userset; mso-width-alt: 7936" width=217><COL style="WIDTH: 261pt; mso-width-source: userset; mso-width-alt: 12726" width=348><COL style="WIDTH: 143pt; mso-width-source: userset; mso-width-alt: 6985" width=191><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl8064 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 72pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #c8c8e6" width=96 height=21>Email</TD><TD class=xl8064 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 104pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #c8c8e6" width=139>Question #</TD><TD class=xl8065 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 191pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ff6464" width=254>Question A</TD><TD class=xl8065 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 163pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ff6464" width=217>Value</TD><TD class=xl8066 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 261pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #b2a1c7" width=348>Combined Value to Use</TD><TD class=xl8066 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 143pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #b2a1c7" width=191>Question Type</TD><TD class=xl8067 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 98pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #b2a1c7" width=131>MQN</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl8058 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>58</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q1</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Response</TD><TD class=xl8060 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Yes</TD><TD class=xl8068 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Yes</TD><TD class=xl8069 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Select One</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl8058 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>58</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q6a</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Application Management</TD><TD class=xl8060 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">1</TD><TD class=xl8068 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Application Management</TD><TD class=xl8069 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Select Many</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q6</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl8058 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>58</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q6b</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Infrastructure Management</TD><TD class=xl8060 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">1</TD><TD class=xl8068 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Infrastructure Management</TD><TD class=xl8069 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Select Many</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q6</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl8058 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>58</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q6c</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">End User Management</TD><TD class=xl8060 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">1</TD><TD class=xl8068 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">End User Management</TD><TD class=xl8069 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Select Many</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q6</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl8058 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>58</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q7</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Application development</TD><TD class=xl8060 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">1</TD><TD class=xl8068 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Application development</TD><TD class=xl8069 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Select Many</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q7</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl8058 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>58</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q7</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Application management/maintenance</TD><TD class=xl8060 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">1</TD><TD class=xl8068 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Application management/maintenance</TD><TD class=xl8069 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Select Many</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q7</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl8058 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>58</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q7</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Test management</TD><TD class=xl8060 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">1</TD><TD class=xl8068 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Test management</TD><TD class=xl8069 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Select Many</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q7</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl8058 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>58</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q8</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Systems management & maintenance</TD><TD class=xl8060 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">1</TD><TD class=xl8068 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Systems management & maintenance</TD><TD class=xl8069 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Select Many</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q8</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl8058 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>58</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q8</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Application hosting / data centre services</TD><TD class=xl8060 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">1</TD><TD class=xl8068 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Application hosting / data centre services</TD><TD class=xl8069 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Select Many</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q8</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl8058 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>58</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q8</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">External networks (WAN, telco)</TD><TD class=xl8060 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">1</TD><TD class=xl8068 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">External networks (WAN, telco)</TD><TD class=xl8069 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Select Many</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q8</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl8058 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>58</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q9</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Desktop services</TD><TD class=xl8060 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">1</TD><TD class=xl8068 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Desktop services</TD><TD class=xl8069 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Select Many</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q9</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl8058 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>58</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q9</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2>Internal networks (LAN, data/voice to the end user)</TD><TD class=xl8060 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">1</TD><TD class=xl8068 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Internal networks (LAN, data/voice to the end user)</TD><TD class=xl8069 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Select Many</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q9</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl8058 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>58</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q9</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Helpdesk</TD><TD class=xl8060 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">1</TD><TD class=xl8068 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Helpdesk</TD><TD class=xl8069 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Select Many</TD><TD class=xl8059 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Q9</TD></TR></TBODY></TABLE>

T. Valko · Jun 7, 2011

Big Bruiser said:
Guys, I am having a similar problem. I tried modifying your formulas (as arrays), but I am still having trouble. I need to count the number of unique emails (column C) if the MQN (column J) equals Q6. Will you help me modify your formula to fit my data?

Try this...

Book1

C

J

1

Email

MQN

2

4

Q1

3

Q6

4

3

Q6

5

Q6

6

2

Q7

7

2

Q7

8

5

Q7

9

2

Q8

10

3

Q8

11

1

Q8

12

1

Q9

13

3

Q9

14

3

Q9

Sheet1

Array entered**:

=SUM(IF(FREQUENCY(IF(J2:J14="Q6",C2:C14),C2:C14),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Unique Value with multiple criteria form other columns

nooracam

New Member

nooracam

New Member

Excel Facts

nooracam

New Member

Aladin Akyurek

MrExcel MVP

T. Valko

Well-known Member

nooracam

New Member

Aladin Akyurek

MrExcel MVP

T. Valko

Well-known Member

T. Valko

Well-known Member

Big Bruiser

New Member

T. Valko

Well-known Member

Similar threads

Forum statistics

Share this page

Unique Value with multiple criteria form other columns

New Member

New Member

Excel Facts

New Member

MrExcel MVP

Well-known Member

New Member

MrExcel MVP

Well-known Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock