Unique Value with multiple criteria form other columns

[nooracam]

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!

 

[Aladin Akyurek]

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!

Try to avoid referencing whole columns...

Control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(
    IF('key acct'!A2:A40000<>"",
    IF(ISNUMBER(MATCH('key acct'!B2:B40000,{7,9,66},0)),
    IF(1-('key acct'!C2:C40000=0),
     MATCH('key acct'!A2:A40000,'key acct'!A2:A40000)))),
       ROW('key acct'!A2:A40000)-ROW('key acct'!A2)+1),1))

 

[T. Valko]

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!

Try it like this...

Use cells to hold the brand variables:

• E2 = 7
• E3 = 9
• E4 = 66

Then...

Array entered**:

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(B2:B10,E2:E4,0)),IF(C2:C10>0,A2:A10)),A2:A10),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.

You do not want to use entire columns as range references with this type of formula! This type of formula is slow to calculate so you don't want to slow things down even further by including cells that don't even contain data.

 

[cgcamal]

Hi nooracam and welcome,

Another option, a little bit large

=IF(SUMPRODUCT(--(B1:B9=7),--(C1:C9>0))>0,1,0)+IF(SUMPRODUCT(--(B1:B9=9),
--(C1:C9>0))>0,1,0)+IF(SUMPRODUCT(--(B1:B9=66),--(C1:C9>0))>0,1,0)

Regards

 

[nooracam]

Thanks all for the awesome replies.

Summary of results:

Aladin's - Your answer ended up providing the exact number on the dot, and it doesn't require any helper cells or other sheets. I'm not sure how or why it works, but it works.

Valko - With the helper cells, your solution provides the unique ocurrences for each of 7,9 66. I need unique ocurrences if they purchased 7, 9, or 66. I will save your solution. I know I will need it at some point.

cgcamal - When I expanded the formula to include the range from 1 to 40000 rows, the answer was '3.' I'm not that sharp on the sumproduct formula, but it seems like it's doing something similar to Valko's solution.

Thanks again for your assistance on this....it's greatly appreciated!!!

 

[T. Valko]

Valko - With the helper cells, your solution provides the unique ocurrences for each of 7,9 66. I need unique ocurrences if they purchased 7, 9, or 66. I will save your solution. I know I will need it at some point.

Hmmm...

Based on the posted sample data the formula I suggested does return the correct result.

No need for helper formulas.

Book1
	A	B	C	D	E
2	1	7	1		7
3	1	2	0		9
4	1	9	1		66
5	4	9	0
6	4	9	1		2
7	4	7	1
8	12	9	0
9	12	66	0
10	12	66	0
Sheet1

The unique entries that meet the conditions are highlighted green.

 

[Aladin Akyurek]

Thanks all for the awesome replies.

Summary of results:

Aladin's - Your answer ended up providing the exact number on the dot, and it doesn't require any helper cells or other sheets. I'm not sure how or why it works, but it works.

Valko - With the helper cells, your solution provides the unique ocurrences for each of 7,9 66. I need unique ocurrences if they purchased 7, 9, or 66. I will save your solution. I know I will need it at some point.

cgcamal - When I expanded the formula to include the range from 1 to 40000 rows, the answer was '3.' I'm not that sharp on the sumproduct formula, but it seems like it's doing something similar to Valko's solution.

Thanks again for your assistance on this....it's greatly appreciated!!!

Great. Thanks for providing feedback.

 

[nooracam]

Trying to add another criterion from another column. This column contains text and can either be "off" or "on ".

I applied the same methodology that Aladin used, but excel is returning an the too few arguments error. Been trying to wotk through this for a few days to no avail.

Below is broken formula:
=SUM(IF(FREQUENCY(
IF('key acct'!K2:K40000<>"",if(istext(match('key acct dump'!E2:E40000,{"off"},0)),
IF(ISNUMBER(MATCH('key acct'!H2:H40000,{7,9,66},0)),
IF(1-('key acct'!Q2:Q40000=0),
MATCH('key acct'!K2:K40000,'key acct'!K2:K40000)))))),
ROW('key acct'!K2:K40000)-ROW('key acct'!K2)+1),1))

 

[T. Valko]

Trying to add another criterion from another column. This column contains text and can either be "off" or "on ".

I applied the same methodology that Aladin used, but excel is returning an the too few arguments error. Been trying to wotk through this for a few days to no avail.

Below is broken formula:
=SUM(IF(FREQUENCY(
IF('key acct'!K2:K40000<>"",if(istext(match('key acct dump'!E2:E40000,{"off"},0)),
IF(ISNUMBER(MATCH('key acct'!H2:H40000,{7,9,66},0)),
IF(1-('key acct'!Q2:Q40000=0),
MATCH('key acct'!K2:K40000,'key acct'!K2:K40000)))))),
ROW('key acct'!K2:K40000)-ROW('key acct'!K2)+1),1))

Maybe this...

Array entered**:

=SUM(IF(FREQUENCY(IF('key acct'!K2:K40000<>"",IF('key acct dump'!E2:E40000="off",IF(ISNUMBER(MATCH('key acct'!H2:H40000,{7,9,66},0)),IF('key acct'!Q2:Q40000<>"",MATCH('key acct'!K2:K40000,'key acct'!K2:K40000))))),ROW('key acct'!K2:K40000)-ROW('key acct'!K2)+1),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.

That new condition you want to include refers to a different sheet.

This takes a long time to calculate. Although I didn't actually time it, it probably took close to a minute to calculate (on my machine).

 

[Aladin Akyurek]

Trying to add another criterion from another column. This column contains text and can either be "off" or "on ".

I applied the same methodology that Aladin used, but excel is returning an the too few arguments error. Been trying to wotk through this for a few days to no avail.

Below is broken formula:
=SUM(IF(FREQUENCY(
IF('key acct'!K2:K40000<>"",if(istext(match('key acct dump'!E2:E40000,{"off"},0)),
IF(ISNUMBER(MATCH('key acct'!H2:H40000,{7,9,66},0)),
IF(1-('key acct'!Q2:Q40000=0),
MATCH('key acct'!K2:K40000,'key acct'!K2:K40000)))))),
ROW('key acct'!K2:K40000)-ROW('key acct'!K2)+1),1))

IsNumber/Match would be more appropriate here if we had a larger set than just 1 item
to compare the E-range with...

Control+shift+enter, not just enter:

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